

Ten Centuries
Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263,
Visits: 1,081


tilew948340 (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???
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




SSCommitted
Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:39 AM
Points: 1,865,
Visits: 593


Good question, great answer
Thanks, Tom
Life: it twists and turns like a twisty turny thing




Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354,
Visits: 1,299


Excellent question but this one got me. At first, I thought they would all return the same results and I was wrong so I definitely learned something new today.
Kudos to the author of this question.




Ten Centuries
Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194,
Visits: 787


Great Question with even more great explanation. Good start for Monday




SSCertifiable
Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:36 PM
Points: 6,002,
Visits: 8,267


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.
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis




SSCrazy
Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817,
Visits: 2,563


Excellent explanation, thanks.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354,
Visits: 1,299


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.




Say Hey Kid
Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668,
Visits: 485





Ten Centuries
Group: General Forum Members
Last Login: Saturday, August 30, 2014 9:14 AM
Points: 1,258,
Visits: 13,554


fantastic question!!!
rfr.ferrari DBA  SQL Server 2008 MCITP  MCTS
remember is live or suffer twice!




Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:54 AM
Points: 554,
Visits: 1,197


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.



