COALESCE Evaluate the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
- 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.
- 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.
- 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.
- 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.
SELECT Name, Class, Color, ItemName,
COALESCE(Class, Color, ItemName) AS HeadName
FROM tblItem;
