Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Scaled-down SQL Expand / Collapse
Author
Message
Posted Monday, March 21, 2011 3:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 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 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
Post #1081144
Posted Monday, March 21, 2011 4:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:49 AM
Points: 1,179, Visits: 783
Great Question with even more great explanation. Good start for Monday
Post #1081217
Posted Monday, March 21, 2011 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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
Post #1081220
Posted Monday, March 21, 2011 7:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
Excellent explanation, thanks.
Post #1081260
Posted Monday, March 21, 2011 7:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 666, Visits: 480
Excellent question!!
Post #1081315
Posted Monday, March 21, 2011 8:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,254, Visits: 13,552
fantastic question!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1081318
Posted Monday, March 21, 2011 8:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:27 AM
Points: 554, Visits: 1,192
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.
Post #1081330
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse