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:

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

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

Chuck said...

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

SQL said...

True should be CURRENT_TIMESTAMP not CURRENT_TIMESTAMP()

You can tell I use GETDATE()

Roji. P. Thomas said...

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

SQL said...

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

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.

Luis said...

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