Google
Google Interview Questions | SQL Server Software | Microsoft SQL Server Books | Denis Gobo's personal blog | Pro SQL server 2005 (Apress)
Top 10 Articles | Add Yourself To My Frappr Map | Ken Henderson Interview | Louis Davidson Interview

Monday, June 12, 2006

COALESCE And ISNULL Differences

I decided to do a quick post about two differences between COALESCE and ISNULL

Run the following block of code


-- The result is 7, integer math
SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)

--The result is 7.5, which is correct
SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)

You will see that the result is not the same ISNULL does integer math while COALESCE does not

COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null

DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)

SELECT @Var4 = 'ABC'


--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)

[Edit] Roji. P. Thomas has an excellent article with even more detailed examples. The links is below.
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html
[/Edit]

8 Comments:

Anonymous Louis Davidson said...

Someday remembering what you have mentioned here would probably save a person hours of wondering why the heck this code isn't exactly right.

Too bad the panic will have set in by then removing all logic as to why it might be occurring :)

9:55 PM  
Blogger SQL said...

Yep, this is just another reason to stay away from proprietary syntax. Although I must admit I do use GETDATE() instead of CURRENT_TIMESTAMP() in my code

5:25 AM  
Anonymous Chuck said...

I thought it was just CURRENT_TIMESTAMP without the () parentheses...

8:10 AM  
Blogger SQL said...

True should be CURRENT_TIMESTAMP not CURRENT_TIMESTAMP()

You can tell I use GETDATE()

9:19 AM  
Blogger Roji. P. Thomas said...

http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html

9:45 AM  
Blogger SQL said...

Roji, I have added your link inside the article...Good stuff!

9:58 AM  
Blogger Luis said...

People should also be aware of the HORRIBLE impact that the use of isnull() or coalesce() can have in queries, in particular ignoring indexes and forcing full table scans. This may vary with SQL Server versions. We have a case where the use of coalesce() changed the duration of a 1 second SQL Server 2000 query to a 27 second, YES - TWENTY-SEVEN!, SQL Server 2005 query. Replacing it with isnull() in 2005 solved it. In SQL Server 2000 it was basically the same.

10:00 AM  
Blogger Luis said...

... I mean in SQL Server 2000 it was the same either way (1 second)

10:01 AM  

Post a Comment

<< Home