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