do you mean assigning a notification when specific contact sends you a sms? or a notification for sms in general.
for a specific contact in handcent go into the thread for that contact and tap the set up button to the right of their name (looks like a gear next to a person), then notification settings, then notification sounds.
to assign a notification for a sms in general go into handcent where it shows all the threads, hit the settings button on the droid (4 lines horizontally), then settings, notification settings, then notification settings again, then notification sound...
this is for handcent, i haven't used the stock app in a looong time.
