SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Expressions


Expressions

Author
Message
aochss
aochss
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 443
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
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 7865
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • wolfkillj
    wolfkillj
    UDP Broadcaster
    UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

    Group: General Forum Members
    Points: 1456 Visits: 2582
    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
    Blog: SQLSouth
    Twitter: @SQLSouth
    Thomas Abraham
    Thomas Abraham
    SSCrazy
    SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

    Group: General Forum Members
    Points: 2717 Visits: 2255
    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.

    Please don't go. The drones need you. They look up to you.
    Connect to me on LinkedIn
    paul.knibbs
    paul.knibbs
    SSCrazy
    SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

    Group: General Forum Members
    Points: 2527 Visits: 6232
    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.
    wolfkillj
    wolfkillj
    UDP Broadcaster
    UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

    Group: General Forum Members
    Points: 1456 Visits: 2582
    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
    Blog: SQLSouth
    Twitter: @SQLSouth
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4116 Visits: 7865
    Jason,

    Simply putting "from sys.columns" after it should be enough to get a plan.

    And, no I don't have an authoritative source - boy I wish I did, so for now let's just say that "it is possible" for this "rewrite" to happen, and from my experience it is therefore an important lesson to learn :-)

    MM


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




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

  • psingla
    psingla
    SSC Eights!
    SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

    Group: General Forum Members
    Points: 880 Visits: 1249
    sestell1 (8/8/2013)
    Excellent question.
    I'm definitely going to remember this in the future.

    +1

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/
    pchirags
    pchirags
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2347 Visits: 615
    Thanks for the awesome question..and explanation..
    twin.devil
    twin.devil
    SSCrazy
    SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

    Group: General Forum Members
    Points: 2354 Visits: 2669
    a bit delayed response from my side .... but a good question !!!
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search