SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by mark.stockwell / Fun with Scale and Precision / Latest Posts

RE: Fun with Scale and Precision

terrykzncs — Thu, 26 May 2011 22:01:51 GMT

I was going to say Good Question and Good Explanation but after reading the posts and trying to understand the rounding, I'm not so sure anymore:doze:Anyway its still a good question and judging by the forum, alot of people like me are struggling or would be ignorant of the outcome. This is an old thread but an important one. ;-)

RE: Fun with Scale and Precision

vignesh 38804 — Wed, 06 Jan 2010 01:44:15 GMT

Any one pls state how round off takes place......:-D

RE: Fun with Scale and Precision

Miles Neale — Thu, 10 Dec 2009 11:36:13 GMT

Thanks. I know that I came to the dance late and that many have commented on this. But this is very useful to those of us dealing with scientific data where the scientists require the precision be clearly represented in the result.very handy in deed!

RE: Fun with Scale and Precision

sknox — Thu, 10 Dec 2009 08:25:06 GMT

Well, on page 2, I have a post which illustrates one potential answer, based primarily on the equations but rewriting them as a procedure ([url]http://www.sqlservercentral.com/Forums/FindPost828945.aspx[/url]), and shortly thereafter nadabadan does effectively the same calculation through a slightly different process ([url]http://www.sqlservercentral.com/Forums/FindPost829119.aspx[/url])I think these two posts come as close as we'll get without a Microsoft developer coming on and explaining their code. :hehe:

RE: Fun with Scale and Precision

RBarryYoung — Thu, 10 Dec 2009 08:11:59 GMT

Leaving my mistaken post out of it then, the outstanding question still is "[i]Why does it choose to truncate to 6 decimal places?[/i]"

RE: Fun with Scale and Precision

sknox — Thu, 10 Dec 2009 06:48:00 GMT

[quote][b]chriscoates (12/10/2009)[/b][hr]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.[/quote]By Jove, I think you've got it! :-PNot what most of us would expect. I'd expect treatment consistent with [url]http://en.wikipedia.org/wiki/Significant_figures[/url]. 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.

RE: Fun with Scale and Precision

sknox — Thu, 10 Dec 2009 06:40:35 GMT

[quote][b]RBarryYoung (12/9/2009)[/b][hr]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...[color="#F0F0FF"]surds[/color][/quote]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:...?

RE: Fun with Scale and Precision

chriscoates — Thu, 10 Dec 2009 04:27:40 GMT

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.

RE: Fun with Scale and Precision

RBarryYoung — Thu, 10 Dec 2009 01:38:00 GMT

[quote][b]sknox (12/9/2009)[/b][hr]I beg to differ, RBarry. MAX() returns the [b]maximum[/b] of the two values. So MAX(6,49) is [b]49[/b].[/quote]Uuhhhh, ... right you are. Never mind. :blush:

RE: Fun with Scale and Precision

sknox — Wed, 09 Dec 2009 17:28:31 GMT

[quote][b]RBarryYoung (12/9/2009)[/b][hr]As for the last part, you appaerntly overlooked applying the MAX(..) function in your calculations:[quote]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.[/quote]should be:[code="plain"]1: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + [i][b]max(6, 49)[/b][/i] = 38 + [i][b]6[/b][/i] = 38 *(applying the rule of absolute 38 max)[/code]and:[quote]The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.[/quote]should be:[code="plain"]2: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = [i][b]max(6, 49)[/b][/i] = [i][b] 6 [/b][/i][/code][/quote]I beg to differ, RBarry. MAX() returns the [b]maximum[/b] of the two values. So MAX(6,49) is [b]49[/b].

RE: Fun with Scale and Precision

RBarryYoung — Wed, 09 Dec 2009 15:00:32 GMT

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...[color="#F0F0FF"]surds[/color]

RE: Fun with Scale and Precision

RBarryYoung — Wed, 09 Dec 2009 14:54:02 GMT

[quote][b]vk-kirov (12/4/2009)[/b][hr][quote][b]stewartc-708166 (12/3/2009)[/b][hr]the explanation re why the rounding off takes place is.....?[/quote]Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": [url]http://technet.microsoft.com/en-us/library/ms190476.aspx[/url]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:[quote]* 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.[/quote]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:[/quote]Thanks for this vk, it was very helpful.As for the last part, you appaerntly overlooked applying the MAX(..) function in your calculations:[quote]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.[/quote]should be:[code="plain"]1: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + [i][b]max(6, 49)[/b][/i] = 38 + [i][b]6[/b][/i] = 38 *(applying the rule of absolute 38 max)[/code]and:[quote]The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.[/quote]should be:[code="plain"]2: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = [i][b]max(6, 49)[/b][/i] = [i][b] 6 [/b][/i][/code]

RE: Fun with Scale and Precision

Bhavesh_Patel — Wed, 09 Dec 2009 03:49:52 GMT

I am still puzzled, why this answer?

RE: Fun with Scale and Precision

sknox — Tue, 08 Dec 2009 12:15:40 GMT

[quote][b]Toreador (12/8/2009)[/b][hr][quote][b]sknox (12/4/2009)[/b][hr][quote][b]Steve Eckhart (12/4/2009)[/b][hr]select cast(1.67574 as decimal(38,10)) = 1.675400000 which has precision 6 and scale 10, right?andselect cast(10000 as decimal(38,10)) = 10000.0000000000 which has precision 1 and scale 10, right?[/quote]wrong, and wrong. Because you're explicitly casting to decimal(38,10), both of your results have precision of 38 and scale of 10 [i]even though they don't apparently require it[/i]. SQL server will not narrow the precision and scale of an explicitly cast decimal result.[/quote]I'm struggling here.Surely that's exactly what SQLServer [i]is[/i] doing?[/quote]No. The equation in the QotD was:CAST( cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) ) Procedurally, this is what SQL Server does:Step 1 is to cast 1.67574 as decimal(38, 10). This explicit cast is not narrowed, which is one reason why the calculation in (3) results in a result that has to be narrowed.Step 2 is to cast 10000 as decimal(38, 10). This explicit cast is not narrowed, which is the other reason why the calculation in (3) results in a result that has to be narrowed.Step 3 is to perform the division. This results in an answer whose precision and scale are beyond the ranges of the decimal data type. So the precision and scale are narrowed as discussed in this topic. Note that technically, this result is [b]not[/b] explicitly cast.Step 4 is to cast the final result (i.e, after the internal narrowing) as decimal(38,10). This explicit cast is also not narrowed, which is why we see the trailing 0s in the result.An explicit cast that falls within the ranges of the decimal data type will not be narrowed. An explicit cast that falls outside the ranges of the decimal data type will result in an error. A calculation which results in precision and scale outside the ranges of the decimal data type will be narrowed to fit within those ranges as discussed.I agree that the explanation on the QotD didn't explain it. That's why I did the research I did.

RE: Fun with Scale and Precision

Toreador — Tue, 08 Dec 2009 09:35:11 GMT

[quote][b]sknox (12/4/2009)[/b][hr][quote][b]Steve Eckhart (12/4/2009)[/b][hr]select cast(1.67574 as decimal(38,10)) = 1.675400000 which has precision 6 and scale 10, right?andselect cast(10000 as decimal(38,10)) = 10000.0000000000 which has precision 1 and scale 10, right?[/quote]wrong, and wrong. Because you're explicitly casting to decimal(38,10), both of your results have precision of 38 and scale of 10 [i]even though they don't apparently require it[/i]. SQL server will not narrow the precision and scale of an explicitly cast decimal result.[/quote]I'm struggling here.Surely that's exactly what SQLServer [i]is[/i] doing?Either way, the explanation attached to the QotD doesn't seem to attempt to explain it - and I'm not surprised!

RE: Fun with Scale and Precision

tbohm — Fri, 04 Dec 2009 11:10:29 GMT

So what we're really saying is that SS doesn't divide correctly.sigh :w00t:

RE: Fun with Scale and Precision

nadabadan — Fri, 04 Dec 2009 11:05:51 GMT

[quote][b]vk-kirov (12/4/2009)[/b][hr][quote][b]stewartc-708166 (12/3/2009)[/b][hr]the explanation re why the rounding off takes place is.....?[/quote]Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": [url]http://technet.microsoft.com/en-us/library/ms190476.aspx[/url]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:[quote]* 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.[/quote]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:[/quote]The ResultingPrecision = 87 and ResultingScale = 49. However, the ResultingPrecision has to be reduced since it is greater than 38. To get to 38, we substract 87 by 49. Since we reduced ResultingPrecision by 49, we need to reduce the ResultingScale by 49 as well. (49 - 49) leaves us a ResultingScale of 0. However max(6, 0) = 6, so the ResultingScale ends up as 6. Therefore, result is truncated ( not rounded ) to 6 decimal digits. So.SELECT cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10))gives us 0.000167 -> precision of 38 and scale of 6soSELECT CAST( cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) ) conv_factorreduces toSELECT CAST( 0.000167 AS DECIMAL(38,10) ) conv_factorwhich gives us 0.0001670000.Took me a while to figure this out. The real question is why SELECT cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) gives us 0.000167. The outer CAST is just a silly distraction.

RE: Fun with Scale and Precision

Steve Eckhart — Fri, 04 Dec 2009 08:28:30 GMT

As someone stated earlier, the link apparently is incorrect since it doesn't enforce the minimum scale you describe here although we're obviously seeing it. I left feedback on the link that the information on division of decimals is incomplete and referenced this discussion.

RE: Fun with Scale and Precision

colin.frame — Fri, 04 Dec 2009 08:19:58 GMT

Nadabanan - thanks for spotting this. Have updated with correct text.

RE: Fun with Scale and Precision

sknox — Fri, 04 Dec 2009 08:06:04 GMT

[quote][b]Steve Eckhart (12/4/2009)[/b][hr]select cast(1.67574 as decimal(38,10)) = 1.675400000 which has precision 6 and scale 10, right?andselect cast(10000 as decimal(38,10)) = 10000.0000000000 which has precision 1 and scale 10, right?[/quote]wrong, and wrong. Because you're explicitly casting to decimal(38,10), both of your results have precision of 38 and scale of 10 [i]even though they don't apparently require it[/i]. SQL server will not narrow the precision and scale of an explicitly cast decimal result.

RE: Fun with Scale and Precision

sknox — Fri, 04 Dec 2009 08:00:29 GMT

[quote][b]vk-kirov (12/4/2009)[/b][hr]...Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": [url]http://technet.microsoft.com/en-us/library/ms190476.aspx[/url]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:[quote]* 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.[/quote]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:[/quote]Well, if the result precision is 87, and the scale is 49, that's a potential of 49 to the right of the decimal point, leaving (87-49)=38 to the left. Now that's an interesting number. If the note were a hard-and-fast rule (i.e, preserve numbers to the left at all costs), the scale would have to be 0. My guess is that the equations aren't exactly as described in Technet. I expect SQL Server actually does the following process (equivalent to the equations except for cases where resulting precision needs to be reduced to 38):[code="sql"](p1 = numerator precision, p2 = denominator precision, pR = result precision; equivalent s for scale)-- min() ensures pR is no greater than 38 as mentioned in the note -- in our example, it becomes 38pR = min(p1-s1+s2+max(6,s1+p2+1),38)-- by subtracting from pR and using max(), we ensure a minimum of 6 digits to the right -- in our example this becomes max(38-(38-10+10),6) = max(0,6) = 6!sR = max(pR-(p1-s1+s2),6) [/code]If that's the case, the 6 digits simply comes from their choice of 6 as the minimum scale when precision has to be truncated.

RE: Fun with Scale and Precision

Steve Eckhart — Fri, 04 Dec 2009 07:44:12 GMT

select cast(1.67574 as decimal(38,10)) = 1.675400000 which has precision 6 and scale 10, right?andselect cast(10000 as decimal(38,10)) = 10000.0000000000 which has precision 1 and scale 10, right?So,[font="Courier New"]p1 - s1 + s2 + max(6, s1 + p2 + 1) = 6 - 10 + 10 + max(6, 10 + 1 + 1) = 6 + 12 = 18.[/font]And the result scale is max(6, s1 + p2 + 1) = max(6, 10 + 1 + 1) = 12.So, where is the rounding happening?In Dude76's step by step we see that d1 / d2 is being rounded and is not returning the promised 18 characters of precision and 12 positions of scale. Why?

RE: Fun with Scale and Precision

Carlo Romagnano — Fri, 04 Dec 2009 07:34:42 GMT

Explanation is in the note.Try to reduce the precision.SELECT CAST(cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) ) conv_factor returns 0.0001670000SELECT CAST(cast(1.67574 as decimal(29,10)) / cast(10000 as decimal(29,10)) AS DECIMAL(29,10) ) conv_factor returns 0.0001675740

RE: Fun with Scale and Precision

mark.hammond — Fri, 04 Dec 2009 07:26:44 GMT

Odd that replacing the CAST(10000,NUMERIC(38,10)) with 10000 gives the more precise result 0.0001675740.Also odd that Sybase 15.0.2 gives the result 0.0001675740 with the original query. You'd think running the SAME query on two ANSI-standard DBMSs would give the SAME result.

RE: Fun with Scale and Precision

nadabadan — Fri, 04 Dec 2009 07:10:49 GMT

[quote][b]colin.frame (12/4/2009)[/b][hr]This explains it:http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspxFor division: scale of the result = s1 + s2 + 1where s1 and s2 are the scales of the initial numbers.[/quote]Nowhere is "s1+s2+1" mentioned in the link provided.

RE: Fun with Scale and Precision

Dude76 — Fri, 04 Dec 2009 05:05:09 GMT

[code="sql"]declare @d1 decimal(38,10), @d2 decimal(38,10)SELECT @d1 = 1.67574, @d2 = 10000SELECT d1 = @d1, d2 = @d2, d1_div_d2 = @d1 / @d2, cast_div = CAST(@d1 / @d2 AS DECIMAL(38,10)), num_div = 1.67574 / 10000.0, cast_num_div = CAST(1.67574 / 10000.0 AS DECIMAL(38,10))[/code]done (i trimed strings to results)[code]d1 d2 d1_div_d2 cast_div num_div cast_num_div------------ ---------------- --------- ------------ -------------- ------------1.6757400000 10000.0000000000 0.000167 0.0001670000 0.000167574000 0.0001675740[/code]:w00t::hehe::crazy::-D

RE: Fun with Scale and Precision

colin.frame — Fri, 04 Dec 2009 02:58:55 GMT

This explains it:http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspxFor division:[EDITED - was incorrect, apologies CFF] scale of the result = max(6, s1 + p2 + 1)where s1 is the scale of the numerator and p2 is the precision of the denominator.

RE: Fun with Scale and Precision

colin.frame — Fri, 04 Dec 2009 02:48:18 GMT

The missing digits is due to the initial casts - with decimal, they are automatically converted to the minimum numeric precision and scale required before the calculation is done, so 1.67574 ends up with a scale of 5 and 10000 with a scale of 0. I would have thought this would lead to an initial result with scale of 5 i.e. 0.00016 and a final result of 0.0001600000 so I'm wondering how the 6th digit gets retained.

RE: Fun with Scale and Precision

archie flockhart — Fri, 04 Dec 2009 01:53:35 GMT

INterestingly, and counter-intuitively, at least for me ...[b]SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,10))[/b]gives this result: .000167While [b]SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,1))[/b]gives this more accurate result.000167574

RE: Fun with Scale and Precision

vk-kirov — Fri, 04 Dec 2009 01:28:18 GMT

[quote][b]stewartc-708166 (12/3/2009)[/b][hr]the explanation re why the rounding off takes place is.....?[/quote]Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": [url]http://technet.microsoft.com/en-us/library/ms190476.aspx[/url]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:[quote]* 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.[/quote]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:

RE: Fun with Scale and Precision

sql.selflearn — Fri, 04 Dec 2009 00:38:07 GMT

0.0001670000

RE: Fun with Scale and Precision

Stewart "Arturius" Campbell — Thu, 03 Dec 2009 23:55:13 GMT

All good and well, however, the explanation re why the rounding off takes place is.....?

Fun with Scale and Precision

Site Owners — Thu, 03 Dec 2009 22:38:50 GMT

Comments posted to this topic are about the item [B]Fun with Scale and Precision[/B]