You set the default notification tone in the main phone settings. You can select individual notification tones in the email account settings or the messaging app or use the default tone.
Handcent SMS (free app) allows you to set a custom tone, LED notification color, & notification icon on the panel for each of your contacts (that is until you run out of options that are available).