The "default" notification sound is established in the phone settings...that's probably the ringtone you're hearing. Depending on the app you're using, you can usually set an individual ringtone for each app via the app's settings (look for notification settings).
I use the Gmail app. and was able to go into the app settings itself and changed the notification tone from there while the system notifications stayed the same. I also use Handcent for texting and was able to change its tone from inside the app as well.