Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Scale and Precision


Fun with Scale and Precision

Author
Message
Bhavesh_Patel
Bhavesh_Patel
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 297
I am still puzzled, why this answer?



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9452 Visits: 9517
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 Ermm


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."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9452 Visits: 9517
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."
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2712
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.
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9452 Visits: 9517
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. Blush

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
chriscoates
chriscoates
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 110
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.
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2712
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:...?
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2712
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! :-P

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.
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9452 Visits: 9517
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."
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2712
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. Hehe
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