<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Duncan Pryde
»
Scaled-down SQL
47 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Scaled-down SQL
Rate Topic
Display Mode
Topic Options
Author
Message
michael.kaufmann
michael.kaufmann
Posted Monday, March 21, 2011 3:53 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263,
Visits: 1,079
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
hodgy
hodgy
Posted Monday, March 21, 2011 4:52 AM
SSCommitted
Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:55 AM
Points: 1,865,
Visits: 556
Good question, great answer
Thanks,
Tom
Life: it twists and turns like a twisty turny thing
Post #1081169
cengland0
cengland0
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
sharath.chalamgari
sharath.chalamgari
Posted Monday, March 21, 2011 6:32 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038,
Visits: 679
Great Question with even more great explanation. Good start for Monday
Post #1081217
Hugo Kornelis
Hugo Kornelis
Posted Monday, March 21, 2011 6:36 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:21 AM
Points: 5,243,
Visits: 7,057
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
Daniel Bowlin
Daniel Bowlin
Posted Monday, March 21, 2011 7:11 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 2,673,
Visits: 2,418
Excellent explanation, thanks.
Post #1081260
cengland0
cengland0
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
Surii
Surii
Posted Monday, March 21, 2011 8:05 AM
SSChasing Mays
Group: General Forum Members
Last Login: Thursday, February 21, 2013 5:19 PM
Points: 658,
Visits: 455
Excellent question!!
Post #1081315
rfr.ferrari
rfr.ferrari
Posted Monday, March 21, 2011 8:12 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 02, 2013 10:51 AM
Points: 1,219,
Visits: 13,507
fantastic question!!!
rfr.ferrari
DBA
- SQL Server
2008
MCITP | MCTS
remember is live or suffer twice!
Post #1081318
Bradley Deem
Bradley Deem
Posted Monday, March 21, 2011 8:30 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:22 AM
Points: 551,
Visits: 1,150
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 »
47 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
