Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Fun with Scale and Precision Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, December 9, 2009 3:49 AM
 SSChasing 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 Patelhttp://bhaveshgpatel.wordpress.com/
Post #831271
 Posted Wednesday, December 9, 2009 2:54 PM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 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.aspxWe 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: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #831871
 Posted Wednesday, December 9, 2009 3:00 PM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 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: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #831874
 Posted Wednesday, December 9, 2009 5:28 PM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:02 AM Points: 1,858, Visits: 2,652
 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 Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 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: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #832042
 Posted Thursday, December 10, 2009 4:27 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, July 6, 2015 4:12 AM Points: 298, 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.000167The outer cast happens after the division, turning this into a ten decimal place number: 0.0001670000For 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
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:02 AM Points: 1,858, Visits: 2,652
 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...surdsSeconded. 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
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:02 AM Points: 1,858, Visits: 2,652
 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.000167The outer cast happens after the division, turning this into a ten decimal place number: 0.0001670000For 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 Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 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: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #832261
 Posted Thursday, December 10, 2009 8:25 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:02 AM Points: 1,858, Visits: 2,652
 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

 Permissions