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


Expressions


Expressions

Author
Message
aochss
aochss
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10745 Visits: 7891
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
    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: 2732 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
    Hall of Fame
    Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

    Group: General Forum Members
    Points: 3853 Visits: 2256
    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
    SSCarpal Tunnel
    SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

    Group: General Forum Members
    Points: 4258 Visits: 6240
    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
    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: 2732 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
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10745 Visits: 7891
    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
    Ten Centuries
    Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

    Group: General Forum Members
    Points: 1414 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.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: 616
    Thanks for the awesome question..and explanation..
    twin.devil
    twin.devil
    SSCertifiable
    SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

    Group: General Forum Members
    Points: 5408 Visits: 2692
    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