Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Expressions Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 7:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:47 AM
Points: 351, Visits: 335
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



Post #1482777
Posted Friday, August 9, 2013 8:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 1,785, Visits: 5,678
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1482787
    Posted Friday, August 9, 2013 9:40 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Wednesday, July 16, 2014 3:21 PM
    Points: 1,210, Visits: 2,512
    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
    Post #1482827
    Posted Friday, August 9, 2013 9:42 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:37 AM
    Points: 1,769, Visits: 2,143
    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
    Post #1482829
    Posted Friday, August 9, 2013 9:44 AM
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Friday, July 25, 2014 3:12 AM
    Points: 1,610, Visits: 5,482
    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.

    Post #1482831
    Posted Friday, August 9, 2013 10:33 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Wednesday, July 16, 2014 3:21 PM
    Points: 1,210, Visits: 2,512
    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
    Post #1482857
    Posted Friday, August 9, 2013 11:51 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 8:50 AM
    Points: 1,785, Visits: 5,678
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1482876
    Posted Friday, August 9, 2013 9:05 PM


    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Thursday, July 10, 2014 8:51 AM
    Points: 608, Visits: 1,025
    sestell1 (8/8/2013)
    Excellent question.
    I'm definitely going to remember this in the future.

    +1


    Pramod
    SQL Server DBA | MCSA SQL Server 2012

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/
    Post #1482998
    Posted Saturday, August 10, 2013 12:54 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Monday, July 7, 2014 9:38 AM
    Points: 1,780, Visits: 582
    Thanks for the awesome question..and explanation..
    Post #1483051
    Posted Tuesday, August 13, 2013 12:27 AM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: 2 days ago @ 5:56 AM
    Points: 632, Visits: 1,152
    a bit delayed response from my side .... but a good question !!!
    Post #1483581
    « Prev Topic | Next Topic »

    Add to briefcase «««23456»»

    Permissions Expand / Collapse