Expressions

  • Nick Doyle

    SSC Eights!

    Points: 926

    Very good QoTD, thanks!

  • SQLRNNR

    SSC Guru

    Points: 281252

    Very good question. I learned something new.

    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

  • Ken Wymore

    SSCoach

    Points: 16642

    Very nice question. Challenging, but still fun. Thanks!

  • sneumersky

    SSCertifiable

    Points: 7667

    ....and I thought my wife asked the toughest questions. My brain came to a screeching halt on this one :w00t:

  • sestell1

    SSChampion

    Points: 10230

    crussell-931424 (8/8/2013)


    Interesting. I didn't realize that nullif is really a case statement and that any function would then get executed multiple times. Of course in this case the function is NewID which of course returns different results every time it is run.

    Yea, the IN clause is also interpreted as a series of CASE statements:

    PRINT CASE WHEN CONVERT(INT, RAND() * 2 + 1) IN (1, 2) THEN 'OK' ELSE 'INVALID' END

    GO 20

  • palotaiarpad

    SSCertifiable

    Points: 5615

    A checksum can be negative. It's new for me.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    sestell1 (8/8/2013)


    crussell-931424 (8/8/2013)


    Interesting. I didn't realize that nullif is really a case statement and that any function would then get executed multiple times. Of course in this case the function is NewID which of course returns different results every time it is run.

    Yea, the IN clause is also interpreted as a series of CASE statements:

    PRINT CASE WHEN CONVERT(INT, RAND() * 2 + 1) IN (1, 2) THEN 'OK' ELSE 'INVALID' END

    GO 20

    It's actually interpreted as a series of conditioned, OR'ed together. The above is equivalent to

    PRINT CASE WHEN CONVERT(INT, RAND() * 2 + 1) = 1

    OR CONVERT(INT, RAND() * 2 + 1) = 2

    THEN 'OK'

    ELSE 'INVALID' END;

    GO 20


    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/

  • PHYData DBA

    SSCertifiable

    Points: 7541

    ok181ko (8/8/2013)


    How about

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

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

    Question was which of the following could be returned.

    Zero is possible but was not in the list being questioned.

    Think about it. Question is correct.

  • PHYData DBA

    SSCertifiable

    Points: 7541

    Nice question. Even Better your explanations.

    If only I had paid enough attention to see it was multiple options....

  • aochss

    SSCommitted

    Points: 1677

    Great question (I like that you included explanations of the build in function).

    However, I cannot see the use/value of the "NULLIF" construct. In what situations would you use something like this?

    Has anyone used this in a production environment?

    Anton

  • mister.magoo

    SSC-Forever

    Points: 47068

    aochss (8/9/2013)


    Great question (I like that you included explanations of the build in function).

    However, I cannot see the use/value of the "NULLIF" construct. In what situations would you use something like this?

    Has anyone used this in a production environment?

    Anton

    Thanks for the kind remarks.

    NULLIF can be very useful, consider this one example:

    SELECT margin_percent = 100 * FLOOR(margin / cost)

    What happens when cost is zero? it blows up!

    Now this

    SELECT margin_percent = 100 * FLOOR(margin / ISNULL(NULLIF(cost,0),margin))

    Now, if the cost is zero it is replaced by the margin value and the calculation returns 100%, which is what you would want.

    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]

  • Jason Wolfkill

    SSCrazy Eights

    Points: 9772

    I figured the answer had something to do with the effect of using non-determinative functions and the NULLIF(), because if I stripped away the ISNULL() and ran this:

    SELECT NULLIF(CONVERT(varchar, SIGN(CHECKSUM(NEWID()))), '-1')

    I could see the NULLIF() returning '1', '-1', and NULL in defiance of an expectation that it would never return '-1'.

    mister.magoo, did you verify that SQL Server rewrites NULLIF() as a CASE expression by direct observation, and if so, how (I couldn't find an execution plan for the query)? Or is this documented somewhere (if so, I must admit that my Google-fu is weak today)?

    Jason Wolfkill

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thanks for the question. I see you've let the adulation already go to your head. 🙂

    The follow-on discussion was great too. Thanks, as always for Hugo's contributions.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • paul.knibbs

    SSCoach

    Points: 15270

    wolfkillj (8/9/2013)

    Or is this documented somewhere (if so, I must admit that my Google-fu is weak today)?

    It's definitely documented (which I wish I'd read before answering the question incorrectly):

    http://technet.microsoft.com/en-us/library/ms177562.aspx

    See the section under "Remarks" near the top.

  • Jason Wolfkill

    SSCrazy Eights

    Points: 9772

    paul.knibbs (8/9/2013)


    wolfkillj (8/9/2013)

    Or is this documented somewhere (if so, I must admit that my Google-fu is weak today)?

    It's definitely documented (which I wish I'd read before answering the question incorrectly):

    http://technet.microsoft.com/en-us/library/ms177562.aspx

    See the section under "Remarks" near the top.

    Hi Paul,

    I did see that remark that "NULLIF is equivalent to a searched CASE expression", but I was curious about whether magoo actually observed that the NULLIF() was converted to the CASE expression in the explanation (i.e., he typed a NULLIF() in his query and saw it represented as the CASE syntax in the execution plan, much like a BETWEEN is represented as a ">=/<=" pair in a predicate) or has an authoritative source that documents the behavior.

    I finally figured out a way to see an execution plan demonstrating how SQL Server treats the NULLIF() using a SELECT . . . FROM a table with several million rows and an index on an integer key with a condition referencing my tally table, like this:

    SELECT integerKey,

    NULLIF(CONVERT(VARCHAR, SIGN(CHECKSUM(NEWID()))),'-1') AS col1

    FROM dbo.millionsOfRows

    WHERE integerKey IN (SELECT n FROM dbo.tally WHERE n BETWEEN 1 AND 1000000)

    This query is complex enough to generate an execution plan, which includes a Compute Scalar with this Defined Value:

    [Expr1005] = Scalar Operator(

    CASE WHEN CONVERT(varchar(30),sign(checksum(newid())),0)='-1' THEN NULL

    ELSE CONVERT(varchar(30),sign(checksum(newid())),0)

    END)

    So that answers my question - it can be observed directly that SQL Server rewrites NULLIF() into a CASE expression.

    Regards,

    Jason

    EDIT: fixed the code formatting

    Jason Wolfkill

  • Viewing 15 posts - 31 through 45 (of 51 total)

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