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

Inconsistent NullIF Behaviour ?

NullIf can be a very handy function sometimes.  For those who have not used it, if the result of the two parameters are equal then null is returned.  So if we execute :

Code Snippet
  1. Select nullif(10,10) as Result1
  2. Select nullif(10,9)  as Result2


We get the result of


For some test data, I required a column that was randomly null.  Using ABS(CHECKSUM(NEWID())) to generate the random value and then modulus 10, to get a random value between 0 and 9 , then passing that into NULLIF as one parameter and 10 as the other, I should of got random values between 1 and 9 with a selection of NULL values.  Here’s the code :

Code Snippet
  1. select top(1000) ROW_NUMBER() over (order by (select null)) as ID,
  2.        nullif(ABS(checksum(newid()))%10,0)  as RandomNonZero
  3. from sys.columns a

And here's the result…


Interesting , does that mean that NULLIF is not working ?  Plainly it is sometimes, after all there are some NULL values. 

Much like in my previous post detailing the differences between ISNULL and COALESCE,  the NULLIF function is expanded by the engine into a case statement as shown below.



So,  its working but the value outputted is not the value tested.


Posted by Joe Celko on 14 August 2010

The definition right out of the ANSI/ISO Standares is:

NULLIF(a, b) ::= (CASE WHEN (a = b) THEN NULL ELSE a END);

I never considered math rounding and conversion errors:

NULLIF (x, 0) versus NULLIF (x, 0.0) versus NULLIF (x, 0.0e0).

Maybe using CAST (<exp> AS INTEGER) for the first parameter is a better idea.

Posted by Andrew Morton-340351 on 14 August 2010

It doesn't work because newid() generates a new id every time it is called, so the first newid() in the case statement is not the same newid() in the else part. The workaround is to set a variable to ABS(checksum(newid())) and use that in the nullif function.

Leave a Comment

Please register or log in to leave a comment.