## Scaled-down SQL

 Author Message michael.kaufmann SSCommitted Group: General Forum Members Points: 1587 Visits: 1082 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 10DECLARE @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 truncateWhy??? 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 hodgy SSCrazy Group: General Forum Members Points: 2663 Visits: 596 Good question, great answer :-)Thanks,Tom Life: it twists and turns like a twisty turny thing cengland0 SSCrazy Group: General Forum Members Points: 2250 Visits: 1300 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. sharath.chalamgari SSCrazy Group: General Forum Members Points: 2006 Visits: 798 Great Question with even more great explanation. Good start for Monday Hugo Kornelis SSCoach Group: General Forum Members Points: 18977 Visits: 12426 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis Daniel Bowlin SSCrazy Eights Group: General Forum Members Points: 8112 Visits: 2629 Excellent explanation, thanks. cengland0 SSCrazy Group: General Forum Members Points: 2250 Visits: 1300 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. Surii SSC Eights! Group: General Forum Members Points: 813 Visits: 489 Excellent question!! rfr.ferrari SSCrazy Group: General Forum Members Points: 2181 Visits: 13639 fantastic question!!! rfr.ferrariDBA - SQL Server 2008MCITP | MCTS remember is live or suffer twice!the period you fastest growing is the most difficult period of your life! Bradley Deem SSC Eights! Group: General Forum Members Points: 989 Visits: 1248 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 expectedBut if you make a mistake and just use the same data type for all... DECIMAL(20,10) then it breaks.12345678.901235I guess we now know why we don't have a Product aggregate function in SQL.