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. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 46 through 46 (of 46 total)
You must be logged in to reply to this topic. Login to reply