May 25, 2011 at 1:09 am
michael.kaufmann (3/21/2011)
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??? :crazy:
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
Nice question! And this is an excellent explanation.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
June 26, 2011 at 4:40 am
Duncan Pryde (3/20/2011)
bitbucket-25253 (3/20/2011)
Excellent question and a more than excellent explanation of why the correct answer is what it is.Thanks - high praise indeed! I must mention though, that when trying to decide how to format the explanation, I came across this excellent post from SQL Kiwi (Paul White?) - which helped me considerably to come up with a clearer and more concise one than I would have done otherwise.
Yes, that's me.
Viewing 2 posts - 46 through 46 (of 46 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy