Nadrek (12/15/2010)
Hardy21 (12/15/2010)
Nice article.I have checked STR() usage in my application but usage is safe because we are not dealing with more than 12 digit number.
May I suggest that if numeric integrity is truly important to your application, you either:
A) switch over to a deterministic method of converting from the current nondeterministic one
or
B) Run a comprehensive test (i.e. verify that every single number from 0 to 999999999999 does, indeed, return what you expect when you use the STR() function), probably from the largest and most risky number to the smallest, ensure all STR() use cannot end up in 13 chars or more, and comment all STR() code with this note, a short description of the flaw, and a link to Jeff's article. You don't want some bright-eyed person later on copying this "perfectly fine, working production code" later for something larger, or updating it with future business requirements.
B1) Repeat your tests every upgrade... just in case.
I expect nondeterministic functions to be nondeterministic. If I want reliability, I choose something that's expected/known/documented/empirically shown to be deterministic.
Heh... obviously B) is the method to avoid. 😀 Thanks for the feedback, Nadrek.
@Hardy21... Nadrek is absolutely correct. A lot of people "leverage" existing code for other things which is why you'll see me get mad as hell when someone jusifies a RBAR method of coding on this forum just because their particular instance is supposedly "guaranteed" to not grow beyond a certain number of rows. If the usage of STR() is easy to find in your case, it's probably pretty easy to change, as well. You already know from the article that changing it from STR() will actually be a bit of a benefit in the area of performance.
--Jeff Moden
Change is inevitable... Change for the better is not.