NULLIF

Categories: News, Professional, Scripts, SSC
Tags: ,
Comments: 3 Comments
Published on: March 30, 2011

Do you use NULLIF?  For me, this command has been seldom used.  Because of that, I have been dabbling with it a bit lately to make sure I have a better understanding of it.

What is it?

This is a function that compares two values.  If the two values are the same, then the result of the operation is a Null matching the datatype of the first expression.  If the two expressions are different, then the result is the value of the first expression.

Seems pretty simple.

In Action

If you look online at MSDN, you can see a couple of examples and a more complete description of what this function is and does.  The MSDN article can be found here.  I wanted something a little different and decided to visualize it differently for my learning.  So here is what I did.

[codesyntax lang=”tsql”]

[/codesyntax]

This will give me a nice random sampling of values to compare.  I simply compare the firstval column to the secondval column.  Both columns are populated with random numbers.  As a part of the result set, I am labeling the comparison field to something descriptive of the field.  I am returning all of the columns so I can see what the values are, and the result of the comparison.  This visualization can help to understand what is happening with the code.  Now I know that if I see a null value, then the two columns are equal.

3 Comments - Leave a comment
  1. Brad Schulz says:

    I rarely use NULLIF at all, but it can come in handy if you have a statement of the type:

    CASE WHEN Something=Value THEN SomeOtherExpression ELSE Something END

    That can translate into:

    COALESCE(NULLIF(Something,Value),SomeOtherExpression)

    Or you can use ISNULL instead of COALESCE if you wish.

    For example, the sys.dm_exec_query_stats DMV has a column called statement_end_offset, which designates the ending position of the query in the batch text, but a -1 indicates the end of the batch.

    So the following will give you the ACTUAL end position:

    COALESCE(NULLIF(statement_end_offset,-1),DATALENGTH(text))

    (where text is the column acquired from the sys.dm_exec_sql_text DMF).

    –Brad

  2. Brad Schulz says:

    One other thing, Jason…

    Even though what I posted with the COALESCE(NULLIF) approach may be convenient for us, it ends up being inconvenient for the optimizer.

    I just looked and the optimizer translates all NULLIF expressions to CASE statements, and it does the same thing with COALESCE.

    So NULLIF(Col1,Col2) is just translated by the optimizer to:

    CASE WHEN Col1=Col2 THEN NULL ELSE Col1 END

    So COALESCE(NULLIF(Col1,Col2),100) is translated to this ugly thing:

    CASE
    WHEN CASE WHEN Col1=Col2 THEN NULL ELSE Col1 END IS NOT NULL
    THEN CASE WHEN Col1=Col2 THEN NULL ELSE Col1 END
    ELSE 100
    END

    We’re only talking about nanoseconds of CPU here, but it sometimes makes me think twice about complicating things for the optimizer.

    –Brad

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
March 2011
M T W T F S S
« Feb   Apr »
 123456
78910111213
14151617181920
21222324252627
28293031  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, November 22, 2014