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 (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218091 Visits: 41995
jimgawn (1/3/2014)
Thank you for your gracious response!

With respect to formatted Date tables, I think a lot of data warehousing folks would beg to differ. People like Ralph Kimball advocate using a date table to (a) simplify query-writing and (b) to reduce computational overhead when running queries and generating reports.


That would certainly be one of the exceptions to the rule. Thanks again for the feedback and welcome aboard!

--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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41190 Visits: 19488
Thank you Jeff for a good "filler", there are many cracks "float"ing around when it comes to math and numbers on the platform. It is also far to easy to miss the fact that float and real are Approximate-number data types, in fact not reliable after the 15th digit as this little factorial sample here shows;


/* Calculate Factorial */
/* SQL Server => 2012 */
DECLARE @TOP BIGINT = 22;

;WITH NUMBERS AS
(
SELECT
CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS FLOAT(53)) AS N
FROM
(VALUES (1), (1), (1), (1), (1), (1), (1), (1)) AS A(N)
,(VALUES (1), (1), (1), (1), (1), (1), (1), (1)) AS B(N)
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (@TOP) ROWS ONLY
)
,N_EXP AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY N2.N ORDER BY (SELECT NULL)) AS N_RID
,N2.N AS N
,EXP(SUM(LOG(N1.N)) OVER (PARTITION BY N2.N)) AS N_EXP
,CAST(EXP(CAST((SUM(CAST(LOG(N1.N) AS DECIMAL(38,16)))
OVER (PARTITION BY N2.N)) AS DECIMAL(38,16)))
AS DECIMAL(38,16)) AS N_DCEXP
FROM NUMBERS N1
CROSS JOIN NUMBERS N2
WHERE N1.N <= N2.N
)
SELECT
NX.N AS N
,NX.N_EXP AS NX_EXP
,CAST(NX.N_EXP AS DECIMAL(38,16)) AS NX_EXPD
,NX.N_DCEXP AS NX_DDEXP
,STR(NX.N_EXP,38,16) AS NX_STR
FROM N_EXP NX
WHERE NX.N_RID = 1;



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96343 Visits: 38981
Eirikur Eiriksson (1/4/2014)
Thank you Jeff for a good "filler", there are many cracks "float"ing around when it comes to math and numbers on the platform. It is also far to easy to miss the fact that float and real are Approximate-number data types, in fact not reliable after the 15th digit as this little factorial sample here shows;


/* Calculate Factorial */
/* SQL Server => 2012 */
DECLARE @TOP BIGINT = 22;

;WITH NUMBERS AS
(
SELECT
CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS FLOAT(53)) AS N
FROM
(VALUES (1), (1), (1), (1), (1), (1), (1), (1)) AS A(N)
,(VALUES (1), (1), (1), (1), (1), (1), (1), (1)) AS B(N)
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (@TOP) ROWS ONLY
)
,N_EXP AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY N2.N ORDER BY (SELECT NULL)) AS N_RID
,N2.N AS N
,EXP(SUM(LOG(N1.N)) OVER (PARTITION BY N2.N)) AS N_EXP
,CAST(EXP(CAST((SUM(CAST(LOG(N1.N) AS DECIMAL(38,16)))
OVER (PARTITION BY N2.N)) AS DECIMAL(38,16)))
AS DECIMAL(38,16)) AS N_DCEXP
FROM NUMBERS N1
CROSS JOIN NUMBERS N2
WHERE N1.N <= N2.N
)
SELECT
NX.N AS N
,NX.N_EXP AS NX_EXP
,CAST(NX.N_EXP AS DECIMAL(38,16)) AS NX_EXPD
,NX.N_DCEXP AS NX_DDEXP
,STR(NX.N_EXP,38,16) AS NX_STR
FROM N_EXP NX
WHERE NX.N_RID = 1;





I have a question about your code above, why the semicolon (Wink before the WITH when the preceding statement is terminated by a semicolon?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41190 Visits: 19488
Hi Lynn,its an old habit I guess rather than belt and suspender kind of thing.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96343 Visits: 38981
Okay, I was just curious as it is a pet peeve of mine. The semicolon is a statement terminator not a statement begininator.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218091 Visits: 41995
Eirikur Eiriksson (1/4/2014)
Thank you Jeff for a good "filler", there are many cracks "float"ing around when it comes to math and numbers on the platform. It is also far to easy to miss the fact that float and real are Approximate-number data types, in fact not reliable after the 15th digit as this little factorial sample here shows;


Thanks for the feedback, Eirikur,

Just to be clear and to prevent other bombastic posts from appearing on this thread, both FLOAT and REAL are entirely reliable with what they were designed to do. The times when they appear to be "unreliable" is when you have full-precision decimal expectations rather than the expectations of what FLOAT and REAL were designed for. The other thing that folks need to understand is that the floating point math behind the scenes of FLOAT and REAL is not base 10... its base 2 or binary. That's why certain numbers can't be duplicated within the precision of FLOAT and REAL.

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218091 Visits: 41995
Lynn Pettis (1/4/2014)
Okay, I was just curious as it is a pet peeve of mine. The semicolon is a statement terminator not a statement begininator.



BWAAA-HAAAA!!!! The use of semicolons at all used to be a pet peeve of mine and still kinda is. I love it when some programming language comes back at me and basically states "Error... you're missing a semicolon at line #nnn". Great... if the language is that bloody smart, why does it need a semicolon to begin with. ;-)

--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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41190 Visits: 19488
Think you nailed it here, just what I was trying to say Whistling
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18091 Visits: 6431
Lynn Pettis (1/4/2014)
Okay, I was just curious as it is a pet peeve of mine. The semicolon is a statement terminator not a statement begininator.



I confess to being one of Lynn's converts. ;-)


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32739 Visits: 8677
One slip Jeff, and I am surprised both that you made it and that no one else mentioned it yet (in my quick review of the thread): you used an Nvarchar(10) to store the output. That introduces a CONVERT_IMPLICIT into the Compute Scalar operation that is unnecessary and which skews the CPU and duration of the runs.

Second, I tried this, expecting the CONVERT_IMPLICIT to float removal to make the STR() run much more efficient. I was mistaken and quite surprised that it did not make it much faster!



CREATE TABLE #t2 (a float)
GO
INSERT #t2
SELECT CAST(somenumber AS float)
FROM #MyHead

DECLARE @BitBucket VARCHAR(10)
SELECT @BitBucket = STR(a,10) FROM #t2



Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
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