Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Hidden Formatting Troubles with STR() (SQL Spackle) Expand / Collapse
Author
Message
Posted Wednesday, December 15, 2010 8:30 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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 of the same type 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
Post #1035196
Posted Wednesday, December 15, 2010 8:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
rmechaber (12/15/2010)
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 of the same type 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


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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035230
Posted Wednesday, December 15, 2010 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
Carlo Romagnano (12/15/2010)
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.


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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035233
Posted Wednesday, December 15, 2010 8:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
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.


Very cool. I love that kind of feedback where folks have actually gone back to check. Well done and thanks.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035234
Posted Wednesday, December 15, 2010 8:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
nigel. (12/15/2010)
Nice little eye opener Jeff, thanks.

I particularly like :

...formatting data in SQL Server is your basic "Bozo-no-no"


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


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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035235
Posted Wednesday, December 15, 2010 9:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035246
Posted Wednesday, December 15, 2010 9:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
Jason Lees-299789 (12/15/2010)
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.


Samuel Vella (12/15/2010)
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!


Scott Arendt (12/15/2010)
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.


Thanks for the feedback folks. I really appreciate it especially when someone posts that they learned something new or useful.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035253
Posted Wednesday, December 15, 2010 9:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
rmechaber (12/15/2010)
Thanks Jeff, for your customary focused, useful style and content!


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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035264
Posted Wednesday, December 15, 2010 9:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 866, Visits: 2,376
Jeff Moden (12/15/2010)
nigel. (12/15/2010)
Nice little eye opener Jeff, thanks.

I particularly like :

...formatting data in SQL Server is your basic "Bozo-no-no"


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


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.


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.
Post #1035284
Posted Wednesday, December 15, 2010 12:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:18 AM
Points: 110, Visits: 783
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".
Post #1035386
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse