Blog Post

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating