March 21, 2011 at 7:11 am
Excellent explanation, thanks.
March 21, 2011 at 7:15 am
Hugo Kornelis (3/21/2011)
Good question; superb explanation!I guess that if the author had included "all of the above" as a fifth answer option, the rate of incorrect answers would have been a lot higher. I guess that is the answer most respondents will first have in mind.
That would have been the option I selected. Or, if the question was one of those "Select All That Apply" and were check boxes instead of radio buttons I would have selected them all.
March 21, 2011 at 8:05 am
Excellent question!!
March 21, 2011 at 8:12 am
fantastic question!!!
March 21, 2011 at 8:30 am
Great question.
I must say, .NET Framework has one up on SQL here. Considering the following VB.NET code.
System.Console.WriteLine(1234567890.123456789D * 0.1D * 0.1D)
returns 12345678.90123456789 as expected which is simple.
DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(2,1) , @value3 DECIMAL(2,1)
Will return 12345678.901234567890 as expected
But if you make a mistake and just use the same data type for all... DECIMAL(20,10) then it breaks.
12345678.901235
I guess we now know why we don't have a Product aggregate function in SQL.
March 21, 2011 at 8:41 am
Good question.Even tough i got wrong i learned one point today.
Thanks for QOTD
Malleswarareddy
I.T.Analyst
MCITP(70-451)
March 21, 2011 at 9:16 am
tilew-948340 (3/20/2011)
I am sorry, but I realy, realy don't understand why D is not good, even if I do your formula.Why is so that D would have a truncate answer and not C?
I mean, both have a precision of 51 and only the scale is different (3 more digit more for C), which might explain something if you could explain my question here:
If I declare D as precision of 23 instead of 25 and still having a scale of 10
DECLARE @value1D DECIMAL(23,10), @value2D DECIMAL(23,10)
it is still a precision over 38, and the scale is still 20 as previous, but the answer is not truncate
Why??? :crazy:
Let's see if we can plug it in to the formula:
D: DECIMAL (51,20) (25+25+1, 10+10)
but we cannot go over precision 38, so the resulting is
Max precision P: 38. So it needs to be reduced by
51-38 = 13.
Since Scale can be defined as
0 <= S <= P
and we reduced the Precision by 13
0 <= (S-13) <= (P-13)
we get
S= 20-13 = 7.
Now, let's do the same to your numbers:
DECLARE @value1D DECIMAL(23,10), @value2D DECIMAL(23,10)
The resulting would be:
DECIMAL (46,20)
Max Precision is 38. So it needs to be reduced by 8
New Scale
S=20-8 = 12
So, with your definition, you have even more wiggly room
Edit: I guess this concern already got answered.
March 21, 2011 at 9:34 am
Good question. I learned something new.
I always knew that decimal and numeric arithmetic did some sort of crazy type forcing that increased rounding errors; but I had never learned exactly how it worked. I guessed (guessing is of course a stupid approach) that it probably damaged both the capability to store digits left of the decimal point and the capability to store digits right of the decimal point, which I now know to be hopelessly wrong - it just goes and maximises the rounding error (which I should have guessed - it's logical).
The new knowledge has reinforced both my intention never to use decimal or numeric datatypes in SQL if their use can sensibly be avoided and my desire for a 128 bit version of float to augment the currectly supported 32 and 64 bit versions).
Edit: given the way decimal and numeric types are defined, I can see where the decision to maximise the rounding error came from; that of course just reinforces my opinion that these are stupid definitions, that the inclusion of scale in the type definition is a relic from the dark ages of computing.
Tom
March 21, 2011 at 11:38 am
Good question. Thank you.
M&M
March 21, 2011 at 12:29 pm
It's unanimous...great question, Duncan! Keep 'em coming.
March 21, 2011 at 1:13 pm
Hugo Kornelis (3/21/2011)
Good question; superb explanation!I guess that if the author had included "all of the above" as a fifth answer option, the rate of incorrect answers would have been a lot higher. I guess that is the answer most respondents will first have in mind.
Funnily enough, the inspiration for this question came from you! I was doing some investigation into the float datatype following a previous QOTD, and came across this blog post from Alex Kuznetsov - with a comment by you that highlighted the rounding issues associated with numerics which I was not previously aware of. I then did a bit of reading and some experimenting and came up with the question.
So thanks!
Regarding the idea of an "all of the above" option - I did consider that, but decided that by not having one it would force people who were unaware of the rounding issue (like me up to a few weeks ago) to do some digging - either in BOL or by experimenting in SSMS.
I'm really chuffed people liked the question anyway, and thanks to everyone for the kind comments.
Duncan
March 21, 2011 at 2:15 pm
michael.kaufmann (3/21/2011)
tilew-948340 (3/20/2011)
I am sorry, but I realy, realy don't understand why D is not good, even if I do your formula.Why is so that D would have a truncate answer and not C?
I mean, both have a precision of 51 and only the scale is different (3 more digit more for C), which might explain something if you could explain my question here:
If I declare D as precision of 23 instead of 25 and still having a scale of 10
DECLARE @value1D DECIMAL(23,10), @value2D DECIMAL(23,10)
it is still a precision over 38, and the scale is still 20 as previous, but the answer is not truncate
Why??? :crazy:
First of all a great big thank you to Duncan for this excellent QotD and the explanation.
Whether the decimal result is 'truncated' or not is a mere mathematical question:
D would result in precision 51 and scale 20; in order to not truncate the integer part of the numeral, SQL Server does the following:
- maximum precision = 38, desired precision is 51 ==> 51 - 38 = 13
- since it doesn't truncate the integer part, the decimal portion (scale) is truncated: 20 - 13 = 7.
Hence the result for option D is DECIMAL(38,7).
If you use a precsion of 23, the math is as follows:
- Precision: 47 - 38 = 9
- Scale: 20 - 9 = 11
- Result: DECIMAL(38,11)
However, as Duncan stated in his explanation, scale will never be less than 6; so the 'minimum' result in regards to scale will always be DECIMAL (38,6).
Regards,
Michael
Excellent explanation. Couldn't have put it better myself.
March 21, 2011 at 4:37 pm
michael.kaufmann (3/21/2011)
tilew-948340 (3/20/2011)
Why??? :crazy:If you use a precsion of 23, the math is as follows:
- Precision: 47 - 38 = 9
- Scale: 20 - 9 = 11
- Result: DECIMAL(38,11)
Ok! Now, I understand why a multiple calculation could truncate the answer
Thank you very very much!
But I still have a question: If you divide by 10 instead of multiply by 0.1, there is no truncation, but it is the same arithmetic calculation.
Is the formula only apply to a multiplication?
DECLARE @OneTenth DECIMAL(25,10)
SET @OneTenth = 0.1
DECLARE @FactorTen DECIMAL(25,10)
SET @FactorTen = 10
DECLARE @value1C DECIMAL(25,10)
SET @value1C = 1234567890.123456789
SELECT @value1C*@OneTenth as CMult --(would give truncate answer 123456789.0123457)
SELECT @value1C/@FactorTen as Cdiv --(would give all numbers and more 123456789.0123456789000)
March 21, 2011 at 4:46 pm
Did you notice that the division increased the scale leaving you with three trailing zeros?
If you look at the link in the explanation you will see that division uses a different formula:
precision: p1 - s1 + s2 + max(6, s1 + p2 + 1)
scale: max(6, s1 + p2 + 1)
Notice that it favors the scale side since division usually results in more fractional digits, where multiplication favors the precision side.
March 21, 2011 at 5:59 pm
UMG Developer (3/21/2011)
Did you notice that the division increased the scale leaving you with three trailing zeros?If you look at the link in the explanation you will see that division uses a different formula:
precision: p1 - s1 + s2 + max(6, s1 + p2 + 1)
scale: max(6, s1 + p2 + 1)
Notice that it favors the scale side since division usually results in more fractional digits, where multiplication favors the precision side.
oyeiuch! I was so focus to understand it that I forgot to follow the link.
I understand now how to calculate it
I know where to look for the formula
Thank you all for your help
🙂
P.S. Now that I know how it is so complicated, I will probably never do a calculation again in SQL :hehe:
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply