YES, iOS 4.2 NOW HAS THE ABILITY TO SET INDIVIDUAL TEXT MESSAGE TONES FOR YOUR CONTACTS!
Sorry for the caps, but its worth screaming. Ever since custom ring tones were possible on the iPhone I have wondered why there was never SMS tones. Hell, a phone that I had 6 years ago had that ability.
One thing that does suck is the fact that you can use a custom tones. You can only use the tones that are available on the iPhone. I hope in the future that the iTunes store will carry tones that we can download and use.