SQLServerCentral » Article Discussions » Article Discussions by Author » Discuss content posted by mark.stockwell » Fun with Scale and PrecisionInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 16 Jan 2018 14:09:10 GMT20Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828643.aspxComments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/67934/">Fun with Scale and Precision</A>[/B]Thu, 26 May 2011 22:01:51 GMTSite OwnersRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost1115997.aspxI 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. ;-)Thu, 26 May 2011 22:01:51 GMTterrykzncsRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost842590.aspxAny one pls state how round off takes place......:-DWed, 06 Jan 2010 01:44:15 GMTvignesh 38804RE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832459.aspxThanks. 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!Thu, 10 Dec 2009 11:36:13 GMTMiles NealeRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832276.aspxWell, 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:Thu, 10 Dec 2009 08:25:06 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832261.aspxLeaving my mistaken post out of it then, the outstanding question still is "[i]Why does it choose to truncate to 6 decimal places?[/i]"Thu, 10 Dec 2009 08:11:59 GMTRBarryYoungRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832163.aspx[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.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.[/quote]
By Jove, I think you've got it! :-P
Not 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.
Thu, 10 Dec 2009 06:48:00 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832156.aspx[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:...?Thu, 10 Dec 2009 06:40:35 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832105.aspxThanks 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.Thu, 10 Dec 2009 04:27:40 GMTchriscoatesRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost832042.aspx[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:Thu, 10 Dec 2009 01:38:00 GMTRBarryYoungRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost831911.aspx[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].
Wed, 09 Dec 2009 17:28:31 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost831874.aspxSteve: (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]Wed, 09 Dec 2009 15:00:32 GMTRBarryYoungRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost831871.aspx[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]
Wed, 09 Dec 2009 14:54:02 GMTRBarryYoungRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost831271.aspxI am still puzzled, why this answer?Wed, 09 Dec 2009 03:49:52 GMTBhavesh_PatelRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost830898.aspx[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?
and
select 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.Tue, 08 Dec 2009 12:15:40 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost830797.aspx[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?
and
select 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!Tue, 08 Dec 2009 09:35:11 GMTToreadorRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost829122.aspxSo what we're really saying is that SS doesn't divide correctly.
sigh :w00t:Fri, 04 Dec 2009 11:10:29 GMTtbohmRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost829119.aspx[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 6
so
SELECT CAST( cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) ) conv_factor
reduces to
SELECT CAST( 0.000167 AS DECIMAL(38,10) ) conv_factor
which 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.Fri, 04 Dec 2009 11:05:51 GMTnadabadanRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828983.aspxAs 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.Fri, 04 Dec 2009 08:28:30 GMTSteve EckhartRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828971.aspxNadabanan - thanks for spotting this. Have updated with correct text.Fri, 04 Dec 2009 08:19:58 GMTcolin.frameRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828955.aspx[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?
and
select 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.Fri, 04 Dec 2009 08:06:04 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828945.aspx[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 38
pR = 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.Fri, 04 Dec 2009 08:00:29 GMTsknoxRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828922.aspxselect cast(1.67574 as decimal(38,10)) = 1.675400000 which has precision 6 and scale 10, right?
and
select 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?Fri, 04 Dec 2009 07:44:12 GMTSteve EckhartRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828913.aspxExplanation 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.0001670000
SELECT CAST(cast(1.67574 as decimal(29,10)) / cast(10000 as decimal(29,10)) AS DECIMAL(29,10) ) conv_factor
returns 0.0001675740
Fri, 04 Dec 2009 07:34:42 GMTCarlo RomagnanoRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828900.aspxOdd 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.Fri, 04 Dec 2009 07:26:44 GMTmark.hammondRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828880.aspx[quote][b]colin.frame (12/4/2009)[/b][hr]This explains it:
http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx
For division:
scale of the result = s1 + s2 + 1
where s1 and s2 are the scales of the initial numbers.[/quote]
Nowhere is "s1+s2+1" mentioned in the link provided.Fri, 04 Dec 2009 07:10:49 GMTnadabadanRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828761.aspx[code="sql"]declare
@d1 decimal(38,10),
@d2 decimal(38,10)
SELECT
@d1 = 1.67574,
@d2 = 10000
SELECT
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::-DFri, 04 Dec 2009 05:05:09 GMTDude76RE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828713.aspxThis explains it:
http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx
For 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.Fri, 04 Dec 2009 02:58:55 GMTcolin.frameRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828708.aspxThe 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.Fri, 04 Dec 2009 02:48:18 GMTcolin.frameRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828686.aspxINterestingly, 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: .000167
While
[b]SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,1))[/b]
gives this more accurate result.000167574Fri, 04 Dec 2009 01:53:35 GMTarchie flockhartRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828680.aspx[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:Fri, 04 Dec 2009 01:28:18 GMTvk-kirovRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828667.aspx0.0001670000Fri, 04 Dec 2009 00:38:07 GMTsql.selflearnRE: Fun with Scale and Precisionhttps://www.sqlservercentral.com/Forums/FindPost828655.aspxAll good and well, however, the explanation re why the rounding off takes place is.....?Thu, 03 Dec 2009 23:55:13 GMTStewart "Arturius" Campbell