dave ballantyne's Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

BRH	8
TSQL	7
#TSQL	5
SQL Server	4
Performance	4
#DBA	3
DBA	2
Tips and Tricks	2
T-SQL	2
Syndicate	2

Archive · View All

July 2010	3
June 2010	3
May 2010	3
August 2010	2
September 2010	1

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (54) · Preferences

SQL Server NULLIF() function - Understand how it works internally, or you might get unexpected results.

Aug 6 2010 8:41AM by dave ballantyne

While the SQL Server NULLIF() function is very handy in many cases, there are a few things you need to keep in mind while using this function. This posts presents some of those scenarios where you might find unexpected results.

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
 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 :

Code Snippet
 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.

Tags: TSQL, BRH, #DBA, #TSQL, DBA,

Previous: SQL Server - Count(*) or Count(1) ? - Which is better?
Next: Kent SQL Server User Group

dave ballantyne

111 · 1% · 462

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

4 Comments

Could it be because the newid() in the ELSE gives a different number than the newid() in the first CASE? I think the problem here is that the newid() gives a different number every time.

commented on Aug 10 2010 8:51PM

johnny

1912 · 0% · 9

It's hard to tell. Is the problem with ISNULL or with NEWID? Take a look...

drop table #MyHead SELECT TOP (1000000) CASE ABS(CHECKSUM(NEWID()))%3 WHEN 0 THEN 'Zero' WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' END AS TestValue INTO #MyHead FROM Master.sys.AllColumns ac1 CROSS JOIN Master.sys.AllColumns ac2

SELECT TestValue, COUNT(*) FROM #MyHead GROUP BY TestValue

No ISNULL in sight.

commented on Aug 11 2010 12:54AM

Jeff Moden

166 · 1% · 291

Hi Jeff /Johnny,

Johnny is right , but ,IMO ,newid() should give a different value on each execution. Otherwise "Select newid(),newid() " would be the same.

Jeff, If you look at the compute scalar operation in the query plan, this is what it is actually executing

(CASE WHEN abs(checksum(newid()))%(3)=(0) THEN 'Zero' ELSE CASE WHEN abs(checksum(newid()))%(3)=(1) THEN 'One' ELSE CASE WHEN abs(checksum(newid()))%(3)=(2) THEN 'Two' ELSE CASE WHEN abs(checksum(newid()))%(3)=(3) THEN 'Three' ELSE NULL END END END END

Which is obvious why you get NULLS and interesting in itself. From a more practical point of view..

case udfLongrunning() when 1 then 'x' when 2 then 'y' when ...........

Would cause the udf to execute on each test !!

commented on Aug 11 2010 2:03AM

dave ballantyne

111 · 1% · 462

Hi Dave,

Heh... yep... pretty obvious there. Definitely NOT what I would expect the code to evaluate as. The good part about it is it's another example of where T-SQL doesn't work as expected or documented and I really like that because it gives me another example to (in a very back-handed way), provide another justification for the use of things like the "quirky update". ;-)

commented on Aug 14 2010 1:04PM

Jeff Moden

166 · 1% · 291

Your Comment

Sign Up or Login to post a comment.

"SQL Server NULLIF() function - Understand how it works internally, or you might get unexpected results." rated 5 out of 5 by 1 readers