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

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