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 «««45678»»

Hidden Formatting Troubles with STR() (SQL Spackle) Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 11: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 @ 8:47 PM
Points: 35,770, Visits: 32,436
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."

(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 #1527679
Posted Saturday, January 4, 2014 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 2,532, Visits: 7,078
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;


Post #1527815
Posted Saturday, January 4, 2014 9:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
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 (;) before the WITH when the preceding statement is terminated by a semicolon?



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)
Post #1527818
Posted Saturday, January 4, 2014 9:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 2,532, Visits: 7,078
Hi Lynn,its an old habit I guess rather than belt and suspender kind of thing.
Post #1527823
Posted Saturday, January 4, 2014 10:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
Okay, I was just curious as it is a pet peeve of mine. The semicolon is a statement terminator not a statement begininator.




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)
Post #1527824
Posted Saturday, January 4, 2014 10: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 @ 8:47 PM
Points: 35,770, Visits: 32,436
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."

(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 #1527833
Posted Saturday, January 4, 2014 11:04 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,770, Visits: 32,436
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."

(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 #1527837
Posted Saturday, January 4, 2014 1:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 2,532, Visits: 7,078
Think you nailed it here, just what I was trying to say
Post #1527861
Posted Saturday, January 4, 2014 8:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1527879
Posted Sunday, January 5, 2014 10:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 4,469, Visits: 6,398
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 at GMail
Post #1527911
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse