Hidden Formatting Troubles with STR() (SQL Spackle)

Question

Hidden Formatting Troubles with STR() (SQL Spackle)

Viewing 15 posts - 31 through 45 (of 80 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

CirquedeSQLeil (12/15/2010)
Thanks Jeff. This is functionality that I have yet to use.

You bet, Jason, and thanks for stopping by. Hopefully I've given most folks incentive to continue to avoid the use of STR().

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

jigneshdpatel (12/15/2010)
Is SQL Server Team listening?

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. 🙂

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jigneshdpatel SSC Rookie Points: 48 More actions · Answer 3

Jeff Moden (12/15/2010)
jigneshdpatel (12/15/2010)
Is SQL Server Team listening?
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. 🙂

Thanks Jeff,

Yes, I am new to this site. I, too, hope to see you guys more and more often.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 4

Thank-you, Jeff! Great series!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Nakul Vachhrajani (12/16/2010)
Thank-you, Jeff! Great series!

Thanks, Nakul... I appreciate the feedback.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

For 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".

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 7

Jeff Moden (12/15/2010)
mtassin (12/15/2010)
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...
:/ 😛
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! 🙂

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. 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

dlinetsky SSC Enthusiast Points: 100 More actions · Answer 8

Thanks Jeff, good to know. I personally have always used this syntax: REPLICATE('0',10-LEN(SomeNumber)) + CAST(somenumber AS VARCHAR(10)), which has always worked well for me, but appears to be slightly slower than yours in this case.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

dlinetsky (12/21/2010)
Thanks Jeff, good to know. I personally have always used this syntax: REPLICATE('0',10-LEN(SomeNumber)) + CAST(somenumber AS VARCHAR(10)), which has always worked well for me, but appears to be slightly slower than yours in this case.

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.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike McIver SSC-Addicted Points: 493 More actions · Answer 10

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$.

Mike
MacroTrenz.Com
LinkedIn

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JHop34 SSC-Addicted Points: 448 More actions · Answer 12

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 SSC Guru Points: 1004356 More actions · Answer 13

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 14

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 SSCommitted Points: 1503 More actions · Answer 15

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.