Differences between ISNULL and COALESCE

I came across a question in the SQL Server MVP newsgroup recently about ISNULL and COALESCE usage. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences.

1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

2. The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

Please note that I am referring to expressions that will alwahys return a non-NULLable value here. Otherwise, you can have ISNULL or COALESCE return NULL value just fine.

3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:

 ISNULL(NULL, NULL) -- is int

COALESCE(NULL, NULL) -- Will throw an error

COALESCE(CAST(NULL as int), NULL) -- it valid and returns int

4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters

5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function 

6.  You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and queries with COALESCE often fare worse here. See below repro script:

use tempdb

go

create table t1 ( i int );

create table t2 ( i int );

create table t3 ( i int );

go

set showplan_text on;

go

select isnull((select i from t1 where t1.i = t2.i), (select max(i) from t3))

from t2;

select coalesce((select i from t1 where t1.i = t2.i), (select max(i) from

t3))

from t2;

go

set showplan_text off;

go

drop table t1, t2;

go