Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

ISNULL() VS. COALESCE()

There are a lot of arguments about which of these to use.  In my opinion, they both have their place.  Here are a few facts about the two functions.

  • ISNULL can accept only two arguments where as COALESCE can accept any number of input parameters
  • ISNULL is not ANSI standard and is proprietary to TSQL.
  • With ISNULL, the datatype of the second argument is converted to equal the data type of the first argument where as COALESCE converts according to data type precedence. 
  • ISNULL is generally accepted to be faster than COALESCE.
  • COALESCE is a much cooler word and will usually earn you either an impressed glance or a blank confused stare when slipped into casual conversation. 

Here are a few examples that demonstrate some of the functional differences between the two conversion methods.

declare @a varchar(5)
select ISNULL(@a, 'ISNULL Test 1') -- Result: ISNUL
SELECT COALESCE(@a, 'COALESCE Test 1') -- Result: COALESCE Test 1

declare @b tinyint
SELECT ISNULL(@b, 99999) -- Result: **Error**
SELECT COALESCE(@b, 99999) -- Result: 9999

declare @c char(5)
SELECT ISNULL(@c, 'a') + 'B' -- Result: a B
SELECT COALESCE(@c, 'a') + 'B' -- Result: aB


Comments

Posted by harsh athalye on 11 February 2010

The only funny thing about COALESCE() is you never get its spelling right, the first time. But jokes apart, you had mentioned all of the valid points here. For speed point, I remember Mladen from SQLTeam has posted the speed test results for ISNULL and COALESCE here - weblogs.sqlteam.com/.../2937.aspx

Posted by Seth Phelabaum on 12 February 2010

I saw those results as well, but just about every other test I've ever seen contradicts them.  Seeing that page before I published this post is actually the main reason I added the qualifier 'generally'.  As always, test test test!

Posted by Jason Brimhall on 14 February 2010

Like many things - there is a tipping point where one is faster than the other and then roles reverse.  I haven't tested, but I can't imagine a statement with isnulls embedded several times over would be faster than using a single coalesce

i.e. isnull(isnull(isnull(isnull(someint,0),0),0),0)

Posted by Anonymous on 21 February 2010

Pingback from  Data Type Precedence and Implicit Conversions | Never Say Never

Posted by Aleksl on 25 February 2010

SQL Server Compact Edition doesn't support IsNull, the supported function is Coalesce

Posted by Brad Schulz on 27 March 2010

Nice summary.

I've seen differing results on speed tests on ISNULL vs COALESCE, but even if ISNULL is faster, it's not *markedly* faster.

One interesting tidbit:  COALESCE(a,b) is internally translated by the optimizer as CASE WHEN a IS NULL THEN b ELSE a END.  You can see this if you look at the properties in a query plan that uses it.

I personally prefer COALESCE, just because of the ANSI standard... plus I can't stand the datatype conversion behavior of ISNULL.

--Brad

Leave a Comment

Please register or log in to leave a comment.