Predict the outcome of the SQL statements

  • bazzkar

    SSC Veteran

    Points: 270

    Comments posted to this topic are about the item Predict the outcome of the SQL statements

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question, thanks!

    Though I think the explanation left a little to be desired. It isn't just that the decimal type was used, it was that the decimal type was used and no precision/scale was specified, so the number got rounded down to 1.0 when it was converted.

    For example this results in the same thing as all of your float examples:

    select Ceiling(convert(decimal(2, 1), 1.09))

    Because with a scale of one specified it gets rounded to 1.1 before it goes to ceiling.

  • Toreador

    SSChampion

    Points: 11220

    Knew the answer but had a brain fart and clicked the wrong option, oops!

    UMG Developer is right about the explanation.

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Good question but I think a better explaination is:

    the default of decimal without parameters is (18,0) thus making 1.09 -> 1 and then the ceiling of 1 is 1

  • John.Norcott

    SSC Journeyman

    Points: 99

    Thank you Old Hand. I knew the ceiling function brought you up to the next integer, but I couldn't figure out why the decimal was any different than the float on this one! You're explanation was what I was missing. For some reason I thought the default precision was 2, not 0. I'll be sure to remember that the next time I use a decimal data type.

  • Toreador

    SSChampion

    Points: 11220

    I hope one thing that everybody has learned from these questions is that you should never rely on default precision for anything - you will inevitably get it wrong sooner or later 🙂

  • TomThomson

    SSC Guru

    Points: 104707

    Good question, but as some have already noted the explanation is a bit lacking.

    Also, the question was made too easy by the absence of the All 2s option as an answer choice - anyone who knows what ceiling means can eliminate all answers but the correct one with knowing anything at all about decimal or its default precision.

    Tom

  • Cliff Jones

    SSChampion

    Points: 10517

    Tom.Thomson (11/2/2010)


    Good question, but as some have already noted the explanation is a bit lacking.

    Also, the question was made too easy by the absence of the All 2s option as an answer choice - anyone who knows what ceiling means can eliminate all answers but the correct one with knowing anything at all about decimal or its default precision.

    Yes, I agree. The best wrong answer was missing.

  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Koen Verbeeck

    SSC Guru

    Points: 258870

    Good question, although it was more about convert than about ceiling.

    A link to the msdn page for convert and decimal:

    http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx (convert)

    http://msdn.microsoft.com/en-us/library/ms187746.aspx (decimal/numeric)

    The last page describes the gotcha of this question in the explanation of the scale.

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

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Nice question. Thanks

    Thanks

Viewing 11 posts - 1 through 11 (of 11 total)

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