handcent can have its own so that covers texts, then all others can have one but i don't think each one can have a specific notification .....yet

 

Handcent will let you do one for each contact, if you want.

The stock messaging app lets you select a notification sound. GMail lets you choose a "ringtone", as do GTalk, Facebook, and Peep.

The default HTC Mail client doesn't let you select a tone, but (of the other onboard notification sources), that's the only one left, so if you set a default notification sound in Settings, you now have a seperate noification sound for every built-in source of notifications (at least, all the ones I can think of).