SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hidden Formatting Troubles with STR() (SQL Spackle)


Hidden Formatting Troubles with STR() (SQL Spackle)

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209847 Visits: 41973
Mike McIver (1/9/2011)
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 Philosophiae Naturalis Principia Mathematica, Author's Preface to the Reader, 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$.


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. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JHop34
JHop34
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 224
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209847 Visits: 41973
Jay Hopping (3/8/2011)
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.


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PHYData DBA
PHYData DBA
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2011 Visits: 537
Jeff - Great article. I never liked STR() in any language. Hope that it just gets deprecated since there are many better ways to do what it does.

I would like to point out that contrary to the wording in your article STR is not actually rounding incorrectly. It is using the simplest and least accurate mode of rounding a Float that IEEE754 allows; round to zero. This mode gives us the common behavior experienced with float-to-integer conversions in mathematics. This is from the Wikipedia (not the best source on earth but one everybody can get to) article on the subject.

round toward zero (truncation; it is similar to the common behavior of float-to-integer conversions, which convert −3.9 to −3 and 3.9 to 3)
http://en.wikipedia.org/wiki/Floating_point#Rounding_modes

I am not sure why anyone would have a function use this rounding mode for float values without documenting it very well. Unless the of course the person that wrote this TSQL function had no control over its documentation:-P
Scott Abrants
Scott Abrants
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 405
Jeff,
Outstanding article, nicely done with great examples.
I am certain this misuse is rampant in the real world and your examples help clarify where to look for these pitfalls.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 2582
There's a use case when STR() works better/is easier than other methods of converting numbers to strings - when the numbers are already typed as float or real. CAST and CONVERT have some limitations on converting float and real types to strings (scroll down to Remarks and see the second subheading, "float and real Styles").

I recently wrote about a real-world use case where STR() avoided a problem caused by these limitations of CAST and CONVERT with float and real values. While the problem could be solved by converting the float/real values to decimal values and then converting them to strings, the STR() function does the same thing without having to code the nested conversions.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
PHYData DBA
PHYData DBA
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2011 Visits: 537
Jeff Moden (1/9/2011)
[quote]Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . .

Jeff Moden (1/9/2011)

BWAA-HAA!!!... having a bad day, Mike?


Take the same advice you dish out so readily. Look at things from a different point of view. ;-)

Wow....
Someone corrects an obvious mistake in your article that that you made, points out your misinformation, and your response is to mock/laugh at them? Did you really accuse this person of not being able to understand a higher purpose of your misinformation? Did you just Chastise them for not understanding your poor word choice or poor research or your own subject matter, as them not seeing things from a different point of view?

This helps me see you from a different point of view Jeff. Very different... w00t
sneumersky
sneumersky
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3152 Visits: 487
I was burned by this 8 years ago.....never again. Caffiene-deprived, Xanax induced, secondhand THC inhalation.....whatever the state (j/k on the chemical reference--maybe). What is amazing to me is how an article, created to help the SQL community, could incite such scathing grandstanding.

You were quite clear in the beginning of your article regarding your topic; so, don't let the negative feedback get you down. We just cannot please all the people all of the time. There is NOTHING ridiculous about this article.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 2582
PHYData DBA (1/2/2014)
Jeff Moden (1/9/2011)
[quote]Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . .

Jeff Moden (1/9/2011)

BWAA-HAA!!!... having a bad day, Mike?


Take the same advice you dish out so readily. Look at things from a different point of view. ;-)

Wow....
Someone corrects an obvious mistake in your article that that you made, points out your misinformation, and your response is to mock/laugh at them? Did you really accuse this person of not being able to understand a higher purpose of your misinformation? Did you just Chastise them for not understanding your poor word choice or poor research or your own subject matter, as them not seeing things from a different point of view?

This helps me see you from a different point of view Jeff. Very different... w00t


Whoa, easy there, PHYData DBA. No need to heat up a three year old flame war. Jeff may have caused some confusion with the phrase "bad rounding", but he made a valid point about the counterintuitive behavior of the STR() function in some cases (a point that obviously enlightened many readers, as previous posts in this thread demonstrate) even if he misapprehended or misrepresented the cause of that behavior. That mistake warrants a correction based on facts, such as your previous post in this thread that pointed out that STR() doesn't round "badly", it just uses the simplest and least accurate method of rounding a float value. I learned something from your post. Mike McIver, on the other hand, dished out a huge helping of sarcasm with a clear insinuation that the author of the article must be stupid, but never even explained *why* the phrase "bad rounding" was wrong. That kind of post comes off as nothing more than the poster's attempt to assert his superiority and doesn't contribute anything of value to the community. I can't blame Jeff for being a little prickly in response to an attack like that, especially considering that he is one of the most scrupulous members of this forum about backing up his statements with real evidence and data. We would all do well to remember that the purpose of these forums is to share knowledge and that petty bickering defeats that purpose.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209847 Visits: 41973
PHYData DBA (1/2/2014)
Jeff Moden (1/9/2011)
[quote]Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . .

Jeff Moden (1/9/2011)

BWAA-HAA!!!... having a bad day, Mike?


Take the same advice you dish out so readily. Look at things from a different point of view. ;-)

Wow....
Someone corrects an obvious mistake in your article that that you made, points out your misinformation, and your response is to mock/laugh at them? Did you really accuse this person of not being able to understand a higher purpose of your misinformation? Did you just Chastise them for not understanding your poor word choice or poor research or your own subject matter, as them not seeing things from a different point of view?

This helps me see you from a different point of view Jeff. Very different... w00t


I never take exception to constructive criticism. My laugh-off was to summarily dismiss words like "ridiculous" and the general tone of that particular post.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search