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

Fun with Scale and Precision Expand / Collapse
Author
Message
Posted Wednesday, December 9, 2009 3:49 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
I am still puzzled, why this answer?



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #831271
Posted Wednesday, December 9, 2009 2:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
vk-kirov (12/4/2009)
stewartc-708166 (12/3/2009)
the explanation re why the rounding off takes place is.....?

Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": http://technet.microsoft.com/en-us/library/ms190476.aspx

We have two numbers of type NUMERIC(38,10), so their precision = 38 and scale = 10.
According to the table from the above link, the result precision is: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + 49 = 87.
The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.

But there is also a note:
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Ok, the result precision (87) is definitely greater than 38, so it was reduced to 38. But why the scale was reduced to 6 – I can't find any explanation


Thanks for this vk, it was very helpful.


As for the last part, you appaerntly overlooked applying the MAX(..) function in your calculations:
According to the table from the above link, the result precision is: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + 49 = 87.

should be:
1:  p1 - s1 + s2 + max(6, s1 + p2 + 1)
= 38 - 10 + 10 + max(6, 10 + 38 + 1)
= 38 + max(6, 49)
= 38 + 6
= 38 *(applying the rule of absolute 38 max)


and:
The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.

should be:
2: max(6, s1 + p2 + 1)
= max(6, 10 + 38 + 1)
= max(6, 49)
= 6




-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #831871
Posted Wednesday, December 9, 2009 3:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Steve: (RE: SSC new site features/wishlist)

This is a perfect example of a thread or post that I would like to be able to tag or remember somehow. Either by adding it to my briefcase, or adding a personal-tag or a public-tag or a special personal file-cabinet or something like that...

surds


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #831874
Posted Wednesday, December 9, 2009 5:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:11 AM
Points: 1,316, Visits: 1,687
RBarryYoung (12/9/2009)
As for the last part, you appaerntly overlooked applying the MAX(..) function in your calculations:
According to the table from the above link, the result precision is: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + 49 = 87.

should be:
1:  p1 - s1 + s2 + max(6, s1 + p2 + 1)
= 38 - 10 + 10 + max(6, 10 + 38 + 1)
= 38 + max(6, 49)
= 38 + 6
= 38 *(applying the rule of absolute 38 max)


and:
The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.

should be:
2: max(6, s1 + p2 + 1)
= max(6, 10 + 38 + 1)
= max(6, 49)
= 6



I beg to differ, RBarry. MAX() returns the maximum of the two values. So MAX(6,49) is 49.
Post #831911
Posted Thursday, December 10, 2009 1:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
sknox (12/9/2009)

I beg to differ, RBarry. MAX() returns the maximum of the two values. So MAX(6,49) is 49.

Uuhhhh, ... right you are. Never mind.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #832042
Posted Thursday, December 10, 2009 4:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 2012 7:25 AM
Points: 298, Visits: 107
Thanks sknox for some good explanations here. To check I've got this straight ...

The internal casts give us 10 decimal places.
That effectively boils down to:

SELECT ( 1.6757400000 / 10000.0000000000)

For the various reasons described above, SQL decides to truncate this to a 6 decimal place number: 0.000167

The outer cast happens after the division, turning this into a ten decimal place number: 0.0001670000

For the record, I think that's a bit pants. Not what I would have expected.

Good QotD. I've learnt something this morning! I'll definitley have to remember this one. My company does a lot of financial transactions, and we always explicitly cast numbers in an effort to retain lots of decimal places, not lose them. We have come across numbers not adding up correctly before, but were generally unable to track the root cause. I've quite often seen numbers multiplied by a factor so that we work with larger numbers. For example, pounds and pence displayed to 6 decimal places are often stored as decimal pence, rather than decimal pounds.
Post #832105
Posted Thursday, December 10, 2009 6:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:11 AM
Points: 1,316, Visits: 1,687
RBarryYoung (12/9/2009)
Steve: (RE: SSC new site features/wishlist)

This is a perfect example of a thread or post that I would like to be able to tag or remember somehow. Either by adding it to my briefcase, or adding a personal-tag or a public-tag or a special personal file-cabinet or something like that...

surds


Seconded. It would also be useful to have something between instant notification (i.e, spam your inbox on popular threads) and no notification at all. Perhaps a daily e-mail notification - one message: The following threads have been updated today:...?
Post #832156
Posted Thursday, December 10, 2009 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:11 AM
Points: 1,316, Visits: 1,687
chriscoates (12/10/2009)
Thanks sknox for some good explanations here. To check I've got this straight ...

The internal casts give us 10 decimal places.
That effectively boils down to:

SELECT ( 1.6757400000 / 10000.0000000000)

For the various reasons described above, SQL decides to truncate this to a 6 decimal place number: 0.000167

The outer cast happens after the division, turning this into a ten decimal place number: 0.0001670000

For the record, I think that's a bit pants. Not what I would have expected.

Good QotD. I've learnt something this morning! I'll definitley have to remember this one. My company does a lot of financial transactions, and we always explicitly cast numbers in an effort to retain lots of decimal places, not lose them. We have come across numbers not adding up correctly before, but were generally unable to track the root cause. I've quite often seen numbers multiplied by a factor so that we work with larger numbers. For example, pounds and pence displayed to 6 decimal places are often stored as decimal pence, rather than decimal pounds.


By Jove, I think you've got it!

Not what most of us would expect. I'd expect treatment consistent with http://en.wikipedia.org/wiki/Significant_figures. I believe it comes down to performance -- it's much faster to apply a simple set of equations than to inspect the values and select the relevant parameters -- especially when there's the base-2 to base-10 conversions necessary to identify the locations of the significant digits.
Post #832163
Posted Thursday, December 10, 2009 8:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Leaving my mistaken post out of it then, the outstanding question still is "Why does it choose to truncate to 6 decimal places?"

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #832261
Posted Thursday, December 10, 2009 8:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:11 AM
Points: 1,316, Visits: 1,687
Well, on page 2, I have a post which illustrates one potential answer, based primarily on the equations but rewriting them as a procedure (http://www.sqlservercentral.com/Forums/FindPost828945.aspx), and shortly thereafter nadabadan does effectively the same calculation through a slightly different process (http://www.sqlservercentral.com/Forums/FindPost829119.aspx)

I think these two posts come as close as we'll get without a Microsoft developer coming on and explaining their code.
Post #832276
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse