InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:01:24 GMT20http://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Jay Hopping (3/8/2011)[/b][hr]Oh crap... I 'discovered' STR about a 18 months back and have been using it as LTRIM(STR(n)) for creating strings..... well, at least my old code was using cast or convert.Thanks Jeff for bringing this to my attention.[/quote]Be careful, now. The code you've written may not produce computational errors due to rounding errors. Check it and make sure. As I indicated in the article, there are places where STR works just fine. And the performance problems of STR may not be a problem for you. If you're stuff is running quickly, there's no sense in possibly making a mistake with another code change.It IS worth double-checking, though.Fri, 11 Mar 2011 06:10:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxOh crap... I 'discovered' STR about a 18 months back and have been using it as LTRIM(STR(n)) for creating strings..... well, at least my old code was using cast or convert.Thanks Jeff for bringing this to my attention.Tue, 08 Mar 2011 22:09:21 GMTJay Hoppinghttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Mike McIver (1/9/2011)[/b][hr]Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . . STR is deterministic!!!!!!!!!!!!!!!!!! Look it up in BOL!As for the article itself . . . I say again "KNOW YOUR DATA TYPES"! Float and Real should automatically catch one's attention. All values may not be represented in certain formats, e.g. one third cannot be precisely represented in decimal format . . . then saying decimal is flawed does not make one insightful."Bad Rounding" . . . Just how is it the article's author knows something that an army of PhD scientists and mathematicians have not realized after well over a half century of computer development?! Moreover, he then judges not just SQL Server but by implication virtually all software products in the world as having flaws. Among others, Float/Real is addressed by:- The Institute of Electrical and Electronics Engineers- American National Standards Institute- International Organization for Standardization- International Electrotechnical Commission Should we let them, electronics manufactures (INTEL & AMD), and all other software companies (ORACLE too) know they have "Bad rounding"? Finally, poor Sir Isaac Newton, the inventor of calculus, never realized he too had "bad rounding" . . . but he did say:"But the errors are not in the art, but in the artificers. He that works with less accuracy is an imperfect mechanic; and if any could work with perfect accuracy, he would be the most perfect mechanic of all . . ."From [i]Philosophiae Naturalis Principia Mathematica, Author's Preface to the Reader[/i], first published 5 July 1687.The point that STR "MAY" be imprecise is valid. But, that being at the extreme margins, the article's tone and approach to the issue is presumptuous, and only serves to promote ignorance and misunderstanding. Here' some advice, especially for the up and coming (it's free so you know what it's worth). This article demonstrates the "inside out view" problem. Making SQL Server or any other one software (or thing) your point of reference limits your potential. Instead develop an "outside in view". All the fundamental knowledge that went into in SQL Server was already discovered or invented. Take the time to do a small amount of "outside" research to develop breath and depth of knowledge. Do this every day . . . over time the effort pay$.[/quote]BWAA-HAA!!!... having a bad day, Mike?It's totally amazing to me that you missed the whole intent of the article. A great number of users don't know that STR makes a conversion to float and that the related "imprecision" (which they will most certainly interpret as "bad rounding") will occur because the function gives no warning.Take the same advice you dish out so readily. Look at things from a different point of view. ;-)Sun, 09 Jan 2011 15:46:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxReally . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . . STR is deterministic!!!!!!!!!!!!!!!!!! Look it up in BOL!As for the article itself . . . I say again "KNOW YOUR DATA TYPES"! Float and Real should automatically catch one's attention. All values may not be represented in certain formats, e.g. one third cannot be precisely represented in decimal format . . . then saying decimal is flawed does not make one insightful."Bad Rounding" . . . Just how is it the article's author knows something that an army of PhD scientists and mathematicians have not realized after well over a half century of computer development?! Moreover, he then judges not just SQL Server but by implication virtually all software products in the world as having flaws. Among others, Float/Real is addressed by:- The Institute of Electrical and Electronics Engineers- American National Standards Institute- International Organization for Standardization- International Electrotechnical Commission Should we let them, electronics manufactures (INTEL & AMD), and all other software companies (ORACLE too) know they have "Bad rounding"? Finally, poor Sir Isaac Newton, the inventor of calculus, never realized he too had "bad rounding" . . . but he did say:"But the errors are not in the art, but in the artificers. He that works with less accuracy is an imperfect mechanic; and if any could work with perfect accuracy, he would be the most perfect mechanic of all . . ."From [i]Philosophiae Naturalis Principia Mathematica, Author's Preface to the Reader[/i], first published 5 July 1687.The point that STR "MAY" be imprecise is valid. But, that being at the extreme margins, the article's tone and approach to the issue is presumptuous, and only serves to promote ignorance and misunderstanding. Here' some advice, especially for the up and coming (it's free so you know what it's worth). This article demonstrates the "inside out view" problem. Making SQL Server or any other one software (or thing) your point of reference limits your potential. Instead develop an "outside in view". All the fundamental knowledge that went into in SQL Server was already discovered or invented. Take the time to do a small amount of "outside" research to develop breath and depth of knowledge. Do this every day . . . over time the effort pay$.Sun, 09 Jan 2011 06:55:40 GMTMike McIverhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]dlinetsky (12/21/2010)[/b][hr]Thanks Jeff, good to know. I personally have always used this syntax: [code="sql"]REPLICATE('0',10-LEN(SomeNumber)) + CAST(somenumber AS VARCHAR(10))[/code], which has always worked well for me, but appears to be slightly slower than yours in this case.[/quote]Thanks for the feedback.The only reason why that's slower is because you're calculating a replication for each row being returned. Hard coding 10 zeroes and using a "RIGHT" is faster because SQL Server simply doesn't have to do quite as many calculations.Wed, 22 Dec 2010 09:31:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxThanks Jeff, good to know. I personally have always used this syntax: [code="sql"]REPLICATE('0',10-LEN(SomeNumber)) + CAST(somenumber AS VARCHAR(10))[/code], which has always worked well for me, but appears to be slightly slower than yours in this case.Tue, 21 Dec 2010 11:43:13 GMTdlinetskyhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Jeff Moden (12/15/2010)[/b][hr][quote][b]mtassin (12/15/2010)[/b][hr]I didn't even know STR() existed... when I need to make strings out of numbers I always use cast and convert.Thanks a lot Jeff... as I said before, I just read a useful article by you, which means now I'll have to deal with STR() in the next 1-3 months...:/ :P[/quote]BWAA-HAAA!!! Now THAT's funny. So far, your predictions have been spot on. Let's hope I've given you cause to avoid the STR() function altogether.Thanks for the feedback, Mark.p.s. I still really like the flag your avatar is sporting. Well done! :-)[/quote]Oh I haven't seen it in any of the SQL code I directly have influence over, and I'll likely never use it in anything I do.What will happen is the developers who make the products we sell probably used it somewhere and I'll have to help them untangle it in a future endeavor.And thanks on the flag... I do my part to help battle RBAR whenever I see it. :)Thu, 16 Dec 2010 07:08:14 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxFor all you relatively new folks, I thought I'd help you head off a future problem if you ever need to ask a T-SQL or Performance related question either on this site or on other sites. You can get some really fast, tested code answers if you follow the suggestions in the two articles in my signature line below. The names of the articles might be a bit of a put-off but the content will really help you when building a post. Also, this site has a bit of a time limit when constructing a post so you might want to build your post in something like NotePad or Word so you don't lose your work. Also, take a look at the IFCodes for formatting on this site. They can really change the way things look on you post to make life easier for those that want to help you. You can "play" with such things by starting a new post, adding IF codes to see what they do, and then hit the "Preview" button. When you're done playing, you can discard your "playing" just by closing the window. For a real post, of course, you'd hit "Post Reply".Thu, 16 Dec 2010 07:05:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Nakul Vachhrajani (12/16/2010)[/b][hr]Thank-you, Jeff! Great series![/quote]Thanks, Nakul... I appreciate the feedback.Thu, 16 Dec 2010 06:57:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxThank-you, Jeff! Great series!Thu, 16 Dec 2010 02:13:52 GMTNakul Vachhrajanihttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Jeff Moden (12/15/2010)[/b][hr][quote][b]jigneshdpatel (12/15/2010)[/b][hr]Is SQL Server Team listening?[/quote]Nah... probably not. What's important is that people who use T-SQL everyday are. :-)I noticed that this was your first post. Let me be the first to tell you "Welcome Aboard!" and we hope to see more of you. :-)[/quote]Thanks Jeff,Yes, I am new to this site. I, too, hope to see you guys more and more often.Wed, 15 Dec 2010 21:46:47 GMTjigneshdpatelhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]jigneshdpatel (12/15/2010)[/b][hr]Is SQL Server Team listening?[/quote]Nah... probably not. What's important is that people who use T-SQL everyday are. :-)I noticed that this was your first post. Let me be the first to tell you "Welcome Aboard!" and we hope to see more of you. :-)Wed, 15 Dec 2010 21:28:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]CirquedeSQLeil (12/15/2010)[/b][hr]Thanks Jeff. This is functionality that I have yet to use.[/quote]You bet, Jason, and thanks for stopping by. Hopefully I've given most folks incentive to continue to avoid the use of STR().Wed, 15 Dec 2010 21:26:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]UMG Developer (12/15/2010)[/b][hr]Like a lot of other people I never even knew of STR(), I have always used CAST/CONVERT with a RIGHT to get the results I needed. But now I know if I should ever run into STR() to be extra careful, and consider changing it to something else.I find your SQL Spackle articles very clear and useful, and look forward to more of them. Thanks![/quote]Exactly... keep doing it the right way and avoid STR() if for no other reason than it being twice as slow. And thanks for the feedback on the "Spackle" series. You good folks are giving me great encouragement to keep writing them. I had no idea that folks would enjoy them as much as everyone says they have.Wed, 15 Dec 2010 21:24:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]mtassin (12/15/2010)[/b][hr]I didn't even know STR() existed... when I need to make strings out of numbers I always use cast and convert.Thanks a lot Jeff... as I said before, I just read a useful article by you, which means now I'll have to deal with STR() in the next 1-3 months...:/ :P[/quote]BWAA-HAAA!!! Now THAT's funny. So far, your predictions have been spot on. Let's hope I've given you cause to avoid the STR() function altogether.Thanks for the feedback, Mark.p.s. I still really like the flag your avatar is sporting. Well done! :-)Wed, 15 Dec 2010 21:20:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]alen teplitsky (12/15/2010)[/b][hr]string formatting is one of my least favorite things to do. it can take hours to get things right which is a waste of time in my opinion.which is why it's nice to have a SSRS installation. any time i can, i whip up a quick report instead of trying to get string formatting straight[/quote]Funny that you should bring that up. "Formatting" is one of the prime reasons I'm no longer a GUI developer. I also don't use SSRS for pretty much the same reason... one person's idea of "pretty" is usually my idea of "OMG! You really want it to look like THAT??? :sick:" :-PWed, 15 Dec 2010 21:18:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Tom Bakerman (12/15/2010)[/b][hr]Nice article Jeff. You would think, that after all this time, some computer scientist somewhere would come up with a decent way to deal with strings. It seems that every language comes with a big Surgeon General's warning "String manipulation may be hazardous to your application".[/quote]Heh... well said. Hence the need for things like Tally tables and the like. Just imagine a world with a decent SPLIT function in T-SQL that you didn't have to write yourself.Thanks for the feedback, Tom. :-)Wed, 15 Dec 2010 21:14:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxIs SQL Server Team listening?Wed, 15 Dec 2010 18:58:15 GMTjigneshdpatelhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Nadrek (12/15/2010)[/b][hr]Formatting in SQL also has advantages in that multiple platforms and languages and programs receive consistent formatting.[/quote]That also "Depends" especially where dates and currency formats are concerned. If folks follow the ISO standard for date formats, then I agree. If the follow local date formatting, that's when you get into deep Kimchi and the world of hurt that follows. Numeric and currency formats can be even worse.Wed, 15 Dec 2010 18:56:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxThanks Jeff. This is functionality that I have yet to use.Wed, 15 Dec 2010 16:37:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxLike a lot of other people I never even knew of STR(), I have always used CAST/CONVERT with a RIGHT to get the results I needed. But now I know if I should ever run into STR() to be extra careful, and consider changing it to something else.I find your SQL Spackle articles very clear and useful, and look forward to more of them. Thanks!Wed, 15 Dec 2010 15:43:28 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxI didn't even know STR() existed... when I need to make strings out of numbers I always use cast and convert.Thanks a lot Jeff... as I said before, I just read a useful article by you, which means now I'll have to deal with STR() in the next 1-3 months...:/ :PWed, 15 Dec 2010 15:28:18 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxstring formatting is one of my least favorite things to do. it can take hours to get things right which is a waste of time in my opinion.which is why it's nice to have a SSRS installation. any time i can, i whip up a quick report instead of trying to get string formatting straightWed, 15 Dec 2010 12:24:16 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxNice article Jeff. You would think, that after all this time, some computer scientist somewhere would come up with a decent way to deal with strings. It seems that every language comes with a big Surgeon General's warning "String manipulation may be hazardous to your application".Wed, 15 Dec 2010 12:18:33 GMTTom Bakermanhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Jeff Moden (12/15/2010)[/b][hr][quote][b]nigel. (12/15/2010)[/b][hr]Nice little eye opener Jeff, thanks.I particularly like :[quote] ...formatting data in SQL Server is your basic "Bozo-no-no" [/quote]Can I quote that the next time I see another question like:"How do I get TSQL to format my datetime column to show just the date/time/whatever ...?" :-)[/quote]Absolutely but don't say "never" in the process because "It Depends". It's usually a horrible idea to burn clock cycles formatting in SQL Server for way too many reasons to list here (including local display settings in a global environment) but there IS the occasional justifiable need... sometimes there is no GUI to do the formatting for you especially if you're writing directly to a file.[/quote]Some shops make extensive use of SQL Server agent jobs to output files directly (as unreliable as agent job failure detection and reporting is, it's still better than Windows Scheduler); and RTRIM() formatting saves considerable network traffic when your database was built by a vendor who didn't understand that VARCHAR() existed. Also, some languages don't really have formatting (most interactive voice response/telephony systems are an oddity in many ways).Formatting in SQL also has advantages in that multiple platforms and languages and programs receive consistent formatting.Wed, 15 Dec 2010 09:52:39 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]rmechaber (12/15/2010)[/b][hr]Thanks Jeff, for your customary focused, useful style and content![/quote]BTW... thanks for the comment above. I've had several folks tell me in the past that I write "too simple" or "too direct". Steve Jones suggested some "single problem" articles such as are in this "spackle" series and my goal is to keep things short, sweet, and direct because not all of us have the time to read a 26 page article every morning. :-)Wed, 15 Dec 2010 09:28:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Jason Lees-299789 (12/15/2010)[/b][hr]Like others Ive always used the CAST or Convert functions for converting numbers to strings and so didnt know the pitfalls of using the STR() function so it was a useful example. [/quote][quote][b]Samuel Vella (12/15/2010)[/b][hr]Looks like I'm another one who's never used the STR() function and having read the article I think I'll keep it that way.At least I have a good reason now and a reference, so thanks![/quote][quote][b]Scott Arendt (12/15/2010)[/b][hr]Thanks Jeff. I always learn something when I read your articles. I don't use STR() and I don't think I'll start now.[/quote]Thanks for the feedback folks. I really appreciate it especially when someone posts that they learned something new or useful. :-)Wed, 15 Dec 2010 09:18:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Nadrek (12/15/2010)[/b][hr][quote][b]Hardy21 (12/15/2010)[/b][hr]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.[/quote]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 oneor 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.[/quote]Heh... obviously B) is the method to avoid. :-D 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.Wed, 15 Dec 2010 09:11:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]nigel. (12/15/2010)[/b][hr]Nice little eye opener Jeff, thanks.I particularly like :[quote] ...formatting data in SQL Server is your basic "Bozo-no-no" [/quote]Can I quote that the next time I see another question like:"How do I get TSQL to format my datetime column to show just the date/time/whatever ...?" :-)[/quote]Absolutely but don't say "never" in the process because "It Depends". It's usually a horrible idea to burn clock cycles formatting in SQL Server for way too many reasons to list here (including local display settings in a global environment) but there IS the occasional justifiable need... sometimes there is no GUI to do the formatting for you especially if you're writing directly to a file.Wed, 15 Dec 2010 08:58:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Hardy21 (12/15/2010)[/b][hr]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.[/quote]Very cool. I love that kind of feedback where folks have actually gone back to check. Well done and thanks.:-)Wed, 15 Dec 2010 08:58:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Carlo Romagnano (12/15/2010)[/b][hr]I always use this this syntax:RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)Because I didn't know that STR() is also used for formatting, I thought it only converts string to number.[/quote]I do similar although usually with leading spaces when I want to right justify for output to a file that requires it (or something similar). Thanks for the feedback and for posting your example. :-)Wed, 15 Dec 2010 08:56:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]rmechaber (12/15/2010)[/b][hr]Thanks Jeff, for your customary focused, useful style and content!<math_complaint>Your article points out a secondary annoyance here in SQL: the ROUND() function returns a value [b]of the same type[/b] as its passed argument. In your examples under "Incorrect Rounding", note that all of the returned values using ROUND(SomeNumber, 2) return 3 decimals. In junior high school, if I answered the test question "Round off 0.325 to 2 decimal places" with "0.330" I would have gotten that one wrong. Significant digits? What's that? That trailing zero means something in science, and its inclusion here is, IMHO, incorrect. The correct answer is 0.33.</math_complaint>Any thoughts on this?Rich[/quote]Thanks for the feedback. You're one of the few to pick up on the fact that the explicit ROUNDing to two decimal places didn't actually change the underlying datatype nor format the output. And, I agree... it's one of those things that just isn't expected.For those that didn't notice, ROUND didn't and doesn't do any formatting or conversion of the underlying datatype. It simply does what it says... it Rounds to a given number of decimal places (or whole number places if you use a minus-length). Because of the original multiplication by 1/1000th, the underlying datatype has 3 decimal places and ROUND doesn't change that. The number is simply rounding with no change in format and no change in datatype.Wed, 15 Dec 2010 08:52:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxThanks Jeff, for your customary focused, useful style and content!<math_complaint>Your article points out a secondary annoyance here in SQL: the ROUND() function returns a value [b]of the same type[/b] as its passed argument. In your examples under "Incorrect Rounding", note that all of the returned values using ROUND(SomeNumber, 2) return 3 decimals. In junior high school, if I answered the test question "Round off 0.325 to 2 decimal places" with "0.330" I would have gotten that one wrong. Significant digits? What's that? That trailing zero means something in science, and its inclusion here is, IMHO, incorrect. The correct answer is 0.33.</math_complaint>Any thoughts on this?RichWed, 15 Dec 2010 08:30:28 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspx[quote][b]Hardy21 (12/15/2010)[/b][hr]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.[/quote]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 oneor 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.Wed, 15 Dec 2010 07:09:03 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxThanks Jeff. I always learn something when I read your articles. I don't use STR() and I don't think I'll start now.Wed, 15 Dec 2010 06:59:36 GMTScott Arendthttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxLooks like I'm another one who's never used the STR() function and having read the article I think I'll keep it that way.At least I have a good reason now and a reference, so thanks!Wed, 15 Dec 2010 04:19:05 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxLike others Ive always used the CAST or Convert functions for converting numbers to strings and so didnt know the pitfalls of using the STR() function so it was a useful example. Wed, 15 Dec 2010 02:44:08 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxNice little eye opener Jeff, thanks.I particularly like :[quote] ...formatting data in SQL Server is your basic "Bozo-no-no" [/quote]Can I quote that the next time I see another question like:"How do I get TSQL to format my datetime column to show just the date/time/whatever ...?" :-)Wed, 15 Dec 2010 02:19:54 GMTnigel.http://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxNice article. I have checked STR() usage in my application but usage is safe because we are not dealing with more than 12 digit number.Wed, 15 Dec 2010 02:15:11 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1034868-203-1.aspxI always use this this syntax:RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)Because I didn't know that STR() is also used for formatting, I thought it only converts string to number.Wed, 15 Dec 2010 01:29:08 GMTCarlo Romagnano