Scaled-down SQL

Question

Post reply

Scaled-down SQL

Duncan Pryde

SSCertifiable

Points: 7956
More actions
March 19, 2011 at 12:24 pm

#248514

Comments posted to this topic are about the item Scaled-down SQL

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Excellent question and a more than excellent explanation of why the correct answer is what it is.

Thanks -

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

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.

😎

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 3

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:

UMG Developer SSChampion Points: 13482 More actions · Answer 4

Thanks for the question, it really does show why we need to pay close attention to the data types we use.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

Excellent question, with an excellent (If I may dare say, better than BOL) explanation!

Have a wonderful day!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 6

Nice question, but a tough one for a Monday 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

Very good question, indeed. +++

Best Regards,

Chris Büttner

Carlo Romagnano SSC-Insane Points: 22747 More actions · Answer 8

Unbelievable, but true!

Thank you for qotd and a clear explanation!

paul s-306273 SSChampion Points: 10730 More actions · Answer 9

Good question - an area often overlooked by developers.

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 10

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

hodgy SSCertifiable Points: 5906 More actions · Answer 11

Good question, great answer 🙂

Thanks,

Tom

Life: it twists and turns like a twisty turny thing

cengland0 SSCertifiable Points: 6102 More actions · Answer 12

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.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 13

Great Question with even more great explanation. Good start for Monday

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/