SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".
--Phil McCracken
Introduction
Most of the time, I try to teach what to do in T-SQL. This time, I'll teach what not to do. We all know that formatting data in SQL Server is your basic "Bozo-no-no" but we sometimes have to do some formatting to output to a file or for some other non-GUI related reason.
The STR() function appears to be mighty handy for formatting. It right justifies. It rounds to a given number of decimal points. It allows you to control the display width of an output column. What more can you ask for? If you use STR() for much more than simple integers or pre-rounded values , the answer might be, "A new job". Take a look...
The Hidden "Fault" with STR()
A lot of people never get into Books Online deep enough to realize that what they're using will make a huge but hidden "crack" in the foundation of their code. If you take a really good look at STR() in Books Online, you'll notice something and may still not understand the ramifications. Look carefully. Do you see it? Do you see the hidden "fault"?
Syntax
STR ( float_expression [ , length [ , decimal ] ] )
Still don't see it? Let me highlight it for you...
Syntax
STR ( float_expression [ , length [ , decimal ] ] )
"float_expression". What that means is that any numeric value that you try to format with STR() is first implicitly converted to the FLOAT data-type. What does that mean to you? Heh... it means huge "cracks" in your code if you're not careful. Let's see what that implicit conversion to FLOAT does to certain values formatted by STR().
Limited Number of Digits
Run the following code. The value in the very first SELECT is the maximum value for BIGINT. The value in the very last SELECT is, in fact, still much larger than an INTEGER. What will be the return?
SELECT 9223372036854775807, STR(9223372036854775807,19) UNION ALL SELECT 922337203685477580, STR(922337203685477580,19) UNION ALL SELECT 92233720368547758, STR(92233720368547758,19) UNION ALL SELECT 9223372036854775, STR(9223372036854775,19) UNION ALL SELECT 922337203685477, STR(922337203685477,19)
Here's the output:
--------------------------------------- ------------------- 9223372036854775807 9223372036854775800 922337203685477580 922337203685477630 92233720368547758 92233720368547760 9223372036854775 9223372036854776 922337203685477 922337203685477
Take a close look and see that, other than being right justified, the STR() conversion returned incorrect values for the right most column except for the last row. The last row, which is the first correctly converted row, has 15 digits which is also the limit for the FLOAT data-type. That's because STR() first converts the value to FLOAT.
The same goes for the right side of the decimal point. Run the following code and tell me what you see:
SELECT .9223372036854775807, STR(.9223372036854775807,19,19) UNION ALL SELECT .922337203685477580, STR(.922337203685477580,19,19) UNION ALL SELECT .92233720368547758, STR(.92233720368547758,19,19) UNION ALL SELECT .9223372036854775, STR(.9223372036854775,19,19) UNION ALL SELECT .922337203685477, STR(.922337203685477,19,19)
Here are the results:
--------------------------------------- ------------------- 0.9223372036854775807 0.9223372036854776 0.9223372036854775800 0.9223372036854777 0.9223372036854775800 0.9223372036854776 0.9223372036854775000 0.9223372036854776 0.9223372036854770000 0.9223372036854770
This time, we see 16 significant digits but the STR() conversion again returned incorrect values for the right-most column and we finally get lucky when we hit only 15 significant digits. I say we got "lucky" because that's not the end of the story...
Incorrect Rounding
It doesn't take many decimal digits to run into rounding errors with STR() formatting. Run the following code to see what I mean:
--===== Typical rounding problems same as FLOAT
WITH
cteGenerateExampleData AS
( --=== Build a million rows that start at 0.001 and end at 1000.000
-- with an increment of 0.001. This only takes a couple of seconds.
SELECT TOP 1000000
SomeNumber = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*0.001
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
,
cteConversions AS
( --=== Let's round the number using ROUND, a DECIMAL conversion, and STR()
SELECT SomeNumber,
SomeRound = ROUND(SomeNumber,2),
SomeDecimal = CAST(SomeNumber AS DECIMAL(10,2)),
SomeSTR = STR(SomeNumber,10,2)
FROM cteGenerateExampleData
) --=== Finally, show where the Decimal and STR() formatted numbers differ
-- from the correctly rounded value.
SELECT SomeNumber,
SomeRound,
SomeDecimal,
SomeSTR,
ErrorReason = ISNULL(CASE WHEN SomeDecimal <> SomeRound THEN 'Bad Decimal Rounding ' END,'')
+ ISNULL(CASE WHEN SomeStr <> SomeRound THEN 'Bad STR Rounding ' END,'')
FROM cteConversions
WHERE SomeDecimal <> SomeRound
OR SomeStr <> SomeRound
;
I don't want to list out all 40,507 rows but here are the first 10:
SomeNumber SomeRound SomeDecimal SomeSTR ErrorReason 0.015 0.020 0.02 0.01 Bad STR Rounding 0.045 0.050 0.05 0.04 Bad STR Rounding 0.075 0.080 0.08 0.07 Bad STR Rounding 0.145 0.150 0.15 0.14 Bad STR Rounding 0.175 0.180 0.18 0.17 Bad STR Rounding 0.205 0.210 0.21 0.20 Bad STR Rounding 0.235 0.240 0.24 0.23 Bad STR Rounding 0.285 0.290 0.29 0.28 Bad STR Rounding 0.295 0.300 0.30 0.29 Bad STR Rounding 0.305 0.310 0.31 0.30 Bad STR Rounding
As you can see, the STR() formatted number doesn't always round correctly. That's because the underlying FLOAT conversion for numbers like 5.145 is actually 5.144999999999999 and that's the cause of the incorrect rounding from the 3rd decimal place. STR() sees the number as 5.144 and rounds it to 5.14 instead of 5.145
If you really need the simplified way that STR() does right justification, here are a couple of rules you should follow...
- Don't use more than 15 digits... ever.
- Don't use the rounding of STR(). Force the correct rounding using ROUND or a proper conversion to DECIMAL. Then use STR() to do the right justification.
Now... before you get all excited about using STR() just because you know the rules, we're still not done...
STR() is... SLOW!
Don't take my word for it though. Run your own test and prove it to yourself with the following code.
--===== Conditionally drop and rebuild a test table in a nice safe place
-- that everyone has. This only takes several seconds.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
SELECT TOP 1000000
SomeNumber = IDENTITY(INT,1,1)
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Declare a variable which is used to take display time out of the picture
DECLARE @BitBucket NVARCHAR(10)
;
--===== Identify the test and enable the timer for display
PRINT '========== Cast, Concatenate, and Size ==========';
SET STATISTICS TIME ON
;
--===== Slightly more complex method for right justification is more than
-- twice as fast as STR() formatting
SELECT @BitBucket = RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)
FROM #MyHead
;
SET STATISTICS TIME OFF
;
--===== Identify the test and enable the timer for display
PRINT '========== The STR() Method is SLOWER ==========';
SET STATISTICS TIME ON
;
SELECT @BitBucket = STR(SomeNumber,10)
FROM #MyHead
;
--===== Turn the "clock" off
SET STATISTICS TIME OFF
;
The results from that bit of computational heaven are as follows. Of course, your times are likely going to be a whole lot faster than what I got from my 8 year old desktop box.
(1000000 row(s) affected) ========== Cast, Concatenate, and Size ========== SQL Server Execution Times: CPU time = 1516 ms, elapsed time = 1540 ms. ========== The STR() Method is SLOWER ========== SQL Server Execution Times: CPU time = 3890 ms, elapsed time = 3995 ms.
Crack filled!
Thanks for listening folks.
--Jeff Moden