October 18, 2011 at 11:36 am
L' Eomot Inversé (10/18/2011)If the source precisions add up to 37 or less, the scale is the sum of the source scales; otherwise it is 6. At least that's what MS says in BoL. It is not what actually happens, which is maybe slightly less stupid than what MS says happens, but I don't know the detail of what actually happens (but it seems to be at best only slightly less stupid that what MS says happens, judging by results of multiplications).
If I was following... the precision was 38+38+1 = 77, but then has to be scaled back by 39 to 38. The scale would be 20+20 = 40, but has to be scaled back by the same 39, so ends up at 1... but has a minimum of 6... so it's six. If the numbers are defined each as DECIMAL(38,23) which folks say on this question results as 'Multiplication result is the same'... would be 23+23=46, backing off the same 39 to 7... which is the minimum precision needed to not round.
Did I get that all right?
October 18, 2011 at 2:43 pm
Where is the minimum of 6 specified? Was I sleeping? I was thinking the same as the previous post but didn't understand where 6 came from?
October 18, 2011 at 3:58 pm
Rich Weissler (10/18/2011)
L' Eomot Inversé (10/18/2011)If the source precisions add up to 37 or less, the scale is the sum of the source scales; otherwise it is 6. At least that's what MS says in BoL. It is not what actually happens, which is maybe slightly less stupid than what MS says happens, but I don't know the detail of what actually happens (but it seems to be at best only slightly less stupid that what MS says happens, judging by results of multiplications).
If I was following... the precision was 38+38+1 = 77, but then has to be scaled back by 39 to 38. The scale would be 20+20 = 40, but has to be scaled back by the same 39, so ends up at 1... but has a minimum of 6... so it's six. If the numbers are defined each as DECIMAL(38,23) which folks say on this question results as 'Multiplication result is the same'... would be 23+23=46, backing off the same 39 to 7... which is the minimum precision needed to not round.
Did I get that all right?
I think you did. Probably.
An interesting result of this algoritm is that multiplication by 1 can cause arithmetic overflow, as can division by 1. To demonstrate this try
DECLARE @a decimal(38,5)
set @a = 100200300400500600700800900000123.0
declare @b-2 decimal(2,0)
set @b-2 = 1
select @a,@b
select @a/@b -- or select @a*@b
You will get a one row result followed by an overfow error message.
It would be possible to have "exact" numerics avoid all these silly anomalies, by calculating the exact result of multiplication (as a 77 digit internal value, which could never be seen by the user), determining whether the integer ceiling of the result of the result is less than 10**38 and if not signalling an arithmetic overflow, and finally presenting the result as a user-visible exact numeric type with precision min(p1+p2+1,38) (and scale s1+s2 or whatever lower number - possible 0 - will fit given the size of the integer part, with the fractional part rounded as needed to fit into that scale - incrementing the integer part if need be to complete the rounding). Avoiding the divide anomalies is much the same, except that the division only has to be done far enough to get the result accurate to 77 digits, not necessarily an exact result. But that would have been too sensible, I guess.
Tom
October 18, 2011 at 11:30 pm
Rich Weissler (10/18/2011)
L' Eomot Inversé (10/18/2011)If the source precisions add up to 37 or less, the scale is the sum of the source scales; otherwise it is 6. At least that's what MS says in BoL. It is not what actually happens, which is maybe slightly less stupid than what MS says happens, but I don't know the detail of what actually happens (but it seems to be at best only slightly less stupid that what MS says happens, judging by results of multiplications).
If I was following... the precision was 38+38+1 = 77, but then has to be scaled back by 39 to 38. The scale would be 20+20 = 40, but has to be scaled back by the same 39, so ends up at 1... but has a minimum of 6... so it's six. If the numbers are defined each as DECIMAL(38,23) which folks say on this question results as 'Multiplication result is the same'... would be 23+23=46, backing off the same 39 to 7... which is the minimum precision needed to not round.
Did I get that all right?
Even I agree with you.. I was thinking the same. 🙂
October 19, 2011 at 9:44 am
Great question and I also learned something not only from the question but from parts of the discussion.
This kind of question really works!
Not all gray hairs are Dinosaurs!
October 25, 2011 at 8:02 am
Greg - KPS, LLC (10/18/2011)
Where is the minimum of 6 specified? Was I sleeping? I was thinking the same as the previous post but didn't understand where 6 came from?
You may want to read Duncan Pryde's excellent QotD on Scaled-down SQL[/url] together with his superb explanation.
It seems to be a fact that scale cannot drop below 6 once two decimals are divided.
(Edit: Upon further experimenting found out that multiplication on SQL Server 2008 R2 behaves a bit differently than dividing the same values.)
Pushing Tom's example even further:
DECLARE @a decimal(38,0)
set @a = 100200300400500600700800900000123
declare @b-2 decimal(2,0)
set @b-2 = 1
select @a,@b
select @a/@b
This will also result in an overflow error message (after the first row with the values of the two variables) as the integer of the first variable has more than 32 digits--and SQL Server does not allow for truncation of the integer part.
Regards,
Michael
October 26, 2011 at 12:13 am
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply