Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

1. Because ISNULL is a function,it is evaluated only once. As described above,
   the input values for the COALESCE expression can be evaluated multiple
   times.
2. Data type determination of the resulting expression is
   different. ISNULL uses the data type of the first parameter,COALESCE
   follows the CASE expression rules and returns the data type of value
   with the highest precedence.
3. The NULLability of the result expression is different for ISNULL and COALESCE. The
   ISNULL return value is always considered NOT NULLable (assuming the return value is a
   non-nullable one) whereas COALESCE with non-null parameters is
   considered to be NULL. 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,creating key constraints or making the return value
   of a scalar UDF deterministic so that it can be indexed as shown in
   the following example.

> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2 
> -- evaluates to NULL. 

> CREATE TABLE #Demo  (  col1 integer NULL,col2 AS COALESCE(col1,0) PRIMARY KEY,col3 AS ISNULL(col1,0)  ); 
> 
> -- This statement succeeds because the nullability of the 
> -- ISNULL function evaluates AS NOT NULL.
> 
> CREATE TABLE #Demo  (  col1 integer NULL,0),> col3 AS ISNULL(col1,0) PRIMARY KEY  );

Validations for ISNULL and
COALESCE are also different. For example,a NULL value for ISNULL is
converted to int whereas for COALESCE,you must provide a data type.
ISNULL takes only 2 parameters whereas COALESCE takes a variable
number of parameters.

资料来源：BOL