## Fun with Scale and Precision

 Author Message Bhavesh_Patel Ten Centuries Group: General Forum Members Points: 1305 Visits: 297 I am still puzzled, why this answer? Bhavesh Patelhttp://bhaveshgpatel.wordpress.com/ RBarryYoung SSC Guru Group: General Forum Members Points: 79852 Visits: 9519 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." RBarryYoung SSC Guru Group: General Forum Members Points: 79852 Visits: 9519 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." sknox SSCertifiable Group: General Forum Members Points: 6921 Visits: 3158 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 SSC Guru Group: General Forum Members Points: 79852 Visits: 9519 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." chriscoates Old Hand Group: General Forum Members Points: 386 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. sknox SSCertifiable Group: General Forum Members Points: 6921 Visits: 3158 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:...? sknox SSCertifiable Group: General Forum Members Points: 6921 Visits: 3158 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! :-PNot 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 SSC Guru Group: General Forum Members Points: 79852 Visits: 9519 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." sknox SSCertifiable Group: General Forum Members Points: 6921 Visits: 3158 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.