Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Scaled-down SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 21, 2011 3:53 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, July 09, 2013 11:12 PM Points: 1,263, Visits: 1,081
 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
Post #1081144
 Posted Monday, March 21, 2011 4:52 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, December 10, 2013 2:24 AM Points: 1,865, Visits: 578
 Good question, great answer Thanks,Tom Life: it twists and turns like a twisty turny thing
Post #1081169
 Posted Monday, March 21, 2011 6:04 AM
 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.
Post #1081195
 Posted Monday, March 21, 2011 6:32 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 05, 2013 3:06 AM Points: 1,097, Visits: 747
 Great Question with even more great explanation. Good start for Monday
Post #1081217
 Posted Monday, March 21, 2011 6:36 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 12:55 PM Points: 5,603, Visits: 7,803
 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
Post #1081220
 Posted Monday, March 21, 2011 7:11 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 10, 2013 2:01 PM Points: 2,780, Visits: 2,518
 Excellent explanation, thanks.
Post #1081260
 Posted Monday, March 21, 2011 7:15 AM
 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.
Post #1081267
 Posted Monday, March 21, 2011 8:05 AM
 SSChasing Mays Group: General Forum Members Last Login: Monday, November 04, 2013 4:02 PM Points: 659, Visits: 474
 Excellent question!!
Post #1081315
 Posted Monday, March 21, 2011 8:12 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, December 06, 2013 8:07 AM Points: 1,253, Visits: 13,537
 fantastic question!!! rfr.ferrariDBA - SQL Server 2008MCITP | MCTS remember is live or suffer twice!
Post #1081318
 Posted Monday, March 21, 2011 8:30 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, November 20, 2013 6:39 AM Points: 551, Visits: 1,164
 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.
Post #1081330

 Permissions