If there isn't a readily available stock notification sound you're happy with, you can always download ringdroid from the market and create one using a song you have saved to the phone, or download zedge as it has a library of literally thousands or more. Unfortunately, i can't offer much more than that, as what you think sounds unique and sounds the same, and what i think sounds unique or sounds the same will be different.
EDIT: oops, sorry...guess i should have read the last post a little more carefully. Yes, km75 is correct; at this time there is no way of setting a specific text tone for a single person on a stock device. I'll look and see if I can find a workaround for you, though.