Pages

Wednesday, September 24, 2008

Differences between ISNULL and COALESCE

I came across a question in the various SQL Server forums, about ISNULL and COALESCE usage. ISNULL and COALESCE can be used to get the same results but there are some differences.

they are
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

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

and the other obvious difference is
ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters

No comments: