Hidden Formatting Troubles with STR() (SQL Spackle)

Question

Hidden Formatting Troubles with STR() (SQL Spackle)

Viewing 15 posts - 16 through 30 (of 80 total)

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

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

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.

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: 1004414 More actions · Answer 2

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.

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: 1004414 More actions · Answer 3

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.

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)

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

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.

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 5

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

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

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

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

:/ 😛

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

UMG Developer SSChampion Points: 13482 More actions · Answer 8

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Thanks Jeff. This is functionality that I have yet to use.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Nadrek (12/15/2010)
Formatting in SQL also has advantages in that multiple platforms and languages and programs receive consistent formatting.

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.

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

jigneshdpatel

SSC Rookie

Points: 48

December 15, 2010 at 6:58 pm

#1263255

Is SQL Server Team listening?

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

Tom Bakerman (12/15/2010)
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".

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

--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: 1004414 More actions · Answer 13

alen teplitsky (12/15/2010)
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

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:" 😛

--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: 1004414 More actions · Answer 14

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

--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: 1004414 More actions · Answer 15

UMG Developer (12/15/2010)
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!

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.

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