Expressions

  • Kwex

    Default port

    Points: 1407

    Only 18% of correct answers! Good question mate ...

  • ok181ko

    SSC Rookie

    Points: 35

    paul.knibbs (8/8/2013)


    ok181ko (8/8/2013)


    How about

    newid() = '00000000-0000-0000-0000-000000000000'

    The question is not correct. Answer is 0 (zero).

    The question asked which of the answers were possible outputs of the statement. It never stated that *all* of the possible outputs were listed as answers. Plus, is it even possible for NEWID() to return a value that's all zeroes?

    And You can prove that this can not be?

  • srienstr

    SSCrazy

    Points: 2403

    ok181ko (8/8/2013)


    paul.knibbs (8/8/2013)


    ok181ko (8/8/2013)


    How about

    newid() = '00000000-0000-0000-0000-000000000000'

    The question is not correct. Answer is 0 (zero).

    The question asked which of the answers were possible outputs of the statement. It never stated that *all* of the possible outputs were listed as answers. Plus, is it even possible for NEWID() to return a value that's all zeroes?

    And You can prove that this can not be?

    I can prove that particular value to be irrelevant:

    select checksum('00000000-0000-0000-0000-000000000000')

    -1448389036

    Of course, this does not mean that there isn't a value for newid that would provide a checksum of 0, only that the value you selected is not it.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    srienstr (8/8/2013)


    select checksum('00000000-0000-0000-0000-000000000000')

    -1448389036

    You're calculating the checksum of a value that just happens to loook remarkably like a uniqueidentifier value - but is just a plain old varchar.

    select checksum(cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) )

    Result: 0

    (on my system)


    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/

  • Tee Time

    Hall of Fame

    Points: 3693

    Excellent question, thanks!

  • srienstr

    SSCrazy

    Points: 2403

    Hugo Kornelis (8/8/2013)


    srienstr (8/8/2013)


    select checksum('00000000-0000-0000-0000-000000000000')

    -1448389036

    You're calculating the checksum of a value that just happens to loook remarkably like a uniqueidentifier value - but is just a plain old varchar.

    select checksum(cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) )

    Result: 0

    (on my system)

    Ah, I had not been aware of that distinction. I haven't even finished my first cup of coffee and I'm already learning, today should be a good day.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • alex.d.garland

    Right there with Babe

    Points: 749

    As many others have said - great question, probably the best I've seen on the site. Challenging without being pedantic.

    I did get it wrong but understand why and definitely feel that I've learnt something knew.

  • TomThomson

    SSC Guru

    Points: 104773

    Good question. I managed to get it wrong by exercising my best carelessness. I really must stop answering questions without first engaging my brain and reading the question properly.

    Tom

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Really good question, thanks.

  • Dana Medley

    SSCertifiable

    Points: 6764

    Good question. I need to stop answering questions before I get woke up.



    Everything is awesome!

  • Ed Wagner

    SSC Guru

    Points: 286982

  • mister.magoo

    SSC-Forever

    Points: 47068

    james.of.rivendell (8/7/2013)


    Moral of today's question. Be careful when using indeterministic functions inside nullif...

    That's not the only place this can happen of course...

    ok181ko (8/8/2013)


    How about

    newid() = '00000000-0000-0000-0000-000000000000'

    The question is not correct. Answer is 0 (zero).

    SQL Server will not produce that value from the NEWID() function as it outputs type 4 uuids, where the first digit of the third section is always "4"

    634FAC37-2B2A-[highlight="#ffff11"]4[/highlight]5DF-8540-032ACE5C3A0B

    However, I do concede that the checksum could possibly produce a zero for one or more guid values, at least I have no evidence to say it will not, so thanks for pointing out that zero may be a valid value as well.

    The intention was to educate about the behaviour rather than about the possible values, so I think it still works on that level 🙂

    Everyone Else

    Nice reviews

    Thanks for the nice feedback, I did try hard to make this question useful and informative, so it is pleasing to hear that so many of you enjoyed it. 🙂

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Error Handler

    Ten Centuries

    Points: 1086

    Nice Question!

    Best,
    Naseer Ahmad
    SQL Server DBA

  • sestell1

    SSChampion

    Points: 10230

    Excellent question.

    I'm definitely going to remember this in the future.

  • Sean Smith

    SSCertifiable

    Points: 5903

    Best QotD to date. Well done!

  • Viewing 15 posts - 16 through 30 (of 51 total)

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