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 8:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 AM
Points: 1,935, Visits: 6,438
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
  • 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: Tuesday, October 28, 2014 12:50 PM
    Points: 1,061, Visits: 2,580
    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


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Tuesday, May 19, 2015 5:20 PM
    Points: 2,074, Visits: 2,247
    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: Today @ 2:39 AM
    Points: 1,693, Visits: 5,817
    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: Tuesday, October 28, 2014 12:50 PM
    Points: 1,061, Visits: 2,580
    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 @ 12:01 AM
    Points: 1,935, Visits: 6,438
    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
  • Post #1482876
    Posted Friday, August 9, 2013 9:05 PM


    Say Hey Kid

    Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

    Group: General Forum Members
    Last Login: Yesterday @ 5:59 AM
    Points: 681, Visits: 1,155
    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/
    Post #1482998
    Posted Saturday, August 10, 2013 12:54 PM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Friday, March 13, 2015 12:40 PM
    Points: 2,070, Visits: 595
    Thanks for the awesome question..and explanation..
    Post #1483051
    Posted Tuesday, August 13, 2013 12:27 AM
    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Yesterday @ 3:39 AM
    Points: 970, Visits: 1,695
    a bit delayed response from my side .... but a good question !!!
    Post #1483581
    Posted Friday, August 16, 2013 1:37 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Wednesday, April 29, 2015 10:28 AM
    Points: 3,595, Visits: 2,701
    Very good question indeed. Had to run the code and go through lots of data online before marking correct answers. Definitely was a new thing to learn.
    Post #1485010
    « Prev Topic | Next Topic »

    Add to briefcase «««23456»»

    Permissions Expand / Collapse