Here's another take on this problem.
Firstly, thanks to all who suggested to turn off the "view in lock screen". This definitely works to remove that overlapping SMS tone. But you'd end upsacrificing the lock screen notifications. Bummer...
There is another anomaly I noticed when I'm in a SMS chat thread itself in which if I am plagued by the overlapping/stammering alert, receiving an SMS would sound like a convoluted sound of the incoming ballon sound mixed with the general SMS alert sound. I got around this problem temporarily by the following method (with the view in lockscreen already turned on):
1 - putting my iphone into standby mode (blank screen).
2 - ask a friend (or use my "other" phone) to send an SMS to my iphone
3 - when the notification appears on the lock screen. slide the "Slide to reply" (or swiping the small SMS icon as it appears also works) to directly go to the SMS app itself.
4 - thereafter, there won't be anymore overlapping/stammering SMS alerts, nor would the SMS alert tone convolute with the polite bubble sound (within the SMS-thread) while inside the SMS app anymore.
Logic-wise, its like... if you have the "View in lock screen" activated for the SMS notification, then it must fulfill its mission in life from the lockscreen to bring you directly to the SMS app. Otherwise, the stammer problem would be lingering around somewhere in the background like a nagging ghost until you actually activate step 3 above and clear its notification buffer/queue.
... but alas, if you'd enter the SMS app via the main screen to check on unread SMSes, then the stammering problem would return (until its accessed via the Lockscreen's "Slide to reply" method).
So a more permanent fix (until the next iOS upgrade, hopefully) would still be to disable the "view in lock screen" toggle. And whether the SMS app is tagged inside the notification Center or not, or alert style, or badge or preview.... doesn't seem to make any difference.
The culprit here is exclusively the "view in lock screen" toggle.
And this seems to be a iPhone4/5.0.1 problem (have verified this with 2 separate iPhone4/5.0.1 on different carriers. The problem is consistent and repeatable). I tried to replicate the above on a 3GS/5.0.1 and there were no such problems.
Maybe someone can try this with a 4S/5.0.1 and share their findings as well.
Hope this helps, and hope someone from Apple is taking notice here so that it helps them replicate the problem and hopefully fix it in the next iOS release.
Cheers.
iPhone 4, iOS 5.0.1