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.
WITH randnums AS (
SELECT TOP 100
RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
,FirstVal = ABS(CHECKSUM(NEWID()))%10+1
,SecVal = ABS(CHECKSUM(NEWID()))%10+1
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
)
SELECT RowNum,FirstVal,SecVal, NULLIF(FirstVal,SecVal) AS 'Null if Equal'
FROM randnums
ORDER BY RowNumThis 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.
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
Great info Brad. It is good to know that I am not the only one that rarely uses NULLIF.
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