The SQL Server functions ISNULL and COALESCE seem to occupy the same space in terms of functionality, only ISNULL is restricted to only two parameters, while COALESCE can take any number of parameters. So why ever use ISNULL? Answers and trivia lie within!
First of all, the simple answer for why to prefer ISNULL over COALESCE when given the choice is that ISNULL tends to produce query plans that are more efficient than COALESCE's. Examine the query plans for the two queries given below to see the difference:
-- These two yield different execution plans.
select a.au_id,
isnull( (select price from titles where title_id = ta.title_id), 0 )
from authors a
join titleauthor ta on a.au_id = ta.au_id
select a.au_id,
coalesce( (select price from titles where title_id = ta.title_id), 0 )
from authors a
join titleauthor ta on a.au_id = ta.au_id
The first query uses one less nested loop than the second, resulting in a lower overall cost. I wouldn't recommend writing queries that nest subqueries within functions anyway, but the example was only intended to show the difference in generated execution plans, not to recommend a certain T-SQL coding style.
The other bit of trivia most people don't know about ISNULL and COALESCE is that the return data type for ISNULL is guaranteed to be the same as the data type of the first parameter. However, the return data type of COALESCE is determined by data type precedence rules (see the Books Online topic "Data Type Precedence"). Therefore, the following queries will produce different output:
declare @Example char(2)
set @Example = null
select isnull( @Example, 'abcde' ), coalesce( @Example, 'abcde' )
The first expression using ISNULL will return 'ab', which is the declared datatype of the first parameter, a char(2). The second expression using COALESCE will return the highest precedence data type, which is the longer string 'abcde.'
Take-home lessons:
- When you only need to coalesce two arguments, use ISNULL instead.
- When using COALESCE, use explicit casting to ensure you get a consistent return data type and also to make the meaning of the code clear to those who are less knowledgeable about the quirks of ISNULL and COALESCE than you are now. ;)