Scaled-down SQL

  • Duncan Pryde

    SSCertifiable

    Points: 7956

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

  • Ron McCullough

    SSC Guru

    Points: 63877

    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

    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

    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

    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: 10160

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

    Very good question, indeed. +++

    Best Regards,

    Chris Bรผttner

  • Carlo Romagnano

    SSC-Insane

    Points: 21830

    Unbelievable, but true!

    Thank you for qotd and a clear explanation!

  • paul s-306273

    SSChampion

    Points: 10602

    Good question - an area often overlooked by developers.

  • michael.kaufmann

    SSCrazy

    Points: 2816

    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: 5735

    Good question, great answer ๐Ÿ™‚

    Thanks,

    Tom

    Life: it twists and turns like a twisty turny thing

  • cengland0

    SSCertifiable

    Points: 6102

    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

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

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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/

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

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