I'm creating a rogue, and the core rule book says that rogues are proficient with all simple weapons (which includes light and heavy crossbows) and hand crossbows. Is there any reason I would want to get a hand crossbow when it costs twice as much as a heavy crossbow and deals half the damage? Is there anything special about it that makes it worth the 100gp other than the fact that its listed as an exotic weapon?
Both light and hand crossbows take less time to reload than a heavy crossbow does.
As far as the tactical advantage of a hand crossbow, you can use it with one hand without a penalty, which allows you to theoretically take advantage of it in applications that would otherwise be difficult. For instance, a light crossbow takes a -2 penalty to fire one-handed, and a heavy crossbow takes a -4 penalty.
If you were to, say, be climbing up a wall or the like, you could use the hand crossbow with just one hand and then fire it without a penalty, though you'd still need to free the other hand to reload.
Combined with sneak attacks, which are how rogues can do a lot of damage, you can actually make good use of a hand crossbow to attack from unexpected, hard to retaliate against positions.
Hand crossbows can also be hidden with Sleight of Hand, and are explicitly mentioned in the text; light crossbows are not technically light weapons (except for firing one with each hand) and so cannot be hidden in this way.
That said, there's not a whole lot of applications where hand crossbows excel, but since 3.5 isn't a game where weapon type is going to be your main source of damage by end-game, sacrificing some damage on the die is not necessarily going to be as painful as one would think, especially since hand crossbows can still carry the same enchantments and other bonuses as other crossbows. The only other sacrifice you make for a hand crossbow is range, which won't typically be an issue because you want to be in that 30 foot sneak attack distance.
Partially. The Hand Crossbow Focus feat rolls Rapid Reload for hand crossbows and Weapon Focus into one feat, which is nice. You then take Crossbow Sniper, increasing your sneak attack range to 60 ft. and adding 50% of your dex bonus to damage rolls. Since Hand Crossbow Focus counts as weapon focus, you skip the Rapid Reload feat in exchange for getting Crossbow Sniper, something that archers can't.
Alternatively, you can try to pick up the Ghostly Reload spell somehow as a constant effect on your weapon, so you can do all of this with a heavy crossbow. Or use the Aptitude Weapon enhancement to apply the above feats to a Heavy or Great Crossbow.
The reasons for hand crossbows being exotic are primarily historical ones, rather than game balance ones. Hand Crossbows were originally a drow-only weapon (Hand Crossbow Focus, for example, is found in the Drow of the Underdark splatbook). While there are benefits, the primary reason was because Hand Crossbows were supposed to be a drow-only weapon that humans don't generally know about or use, with the exception of sneaky types such as the rogue.