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 :
- Select nullif(10,10) as Result1
- 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 :
- select top(1000) ROW_NUMBER() over (order by (select null)) as ID,
- nullif(ABS(checksum(newid()))%10,0) as RandomNonZero
- 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.
