ISNULL vs COALESCE: Not as Interchangeable as They Look

You swap ISNULL for COALESCE in a query, or the other way around, because you have heard they do the same thing. Most days they return the same value and nothing looks different. Then a default string comes back truncated, or a computed column you expected to be NOT NULL reports as nullable, or a subquery you wrote once turns up more than once in the plan. The two functions return the same value often enough to blur together, and they differ on type, nullability, and evaluation in ways that matter when the details do.

This is Part 7 of The UNKNOWN Problem, a series on NULL traps in T-SQL. The other parts are about SQL treating a NULL differently than you expected. This one is about the two functions you reach for to handle a NULL, and the ways they do not behave the same.^[1]

The first difference is the result type and length. ISNULL takes the data type of its first argument and forces the whole result into it.^[2] COALESCE follows data-type precedence across all its arguments and picks the type with the highest precedence.^[3] When the first argument is narrower than the replacement, ISNULL truncates.

ISNULL sized the result as varchar(3) from the first argument and cut 'unknown' down to unk. In this example COALESCE returns varchar(7), long enough to hold unknown, because it sizes the result from all its string arguments. SQL Server returns the truncated ISNULL value with no error, so the missing characters are easy to miss when the replacement is a longer literal or a wider column than the one you are defaulting.

The second difference is the nullability recorded in the result metadata, which matters in a computed column or a table built with SELECT ... INTO. ISNULL(nullable_expr, non_null_value) is recorded as NOT NULL, because the replacement removes the possibility of a NULL result. COALESCE(nullable_expr, non_null_value) is recorded as nullable, even though it cannot actually return NULL when the last argument is a non-NULL constant. That metadata difference can decide whether a computed column can serve as a key or carry a NOT NULL constraint. Persisting or indexing a computed column has other requirements too, such as determinism, so nullability is not the only factor.

The third difference is how often an argument is evaluated. COALESCE is defined as a CASE expression, so a non-final argument can be evaluated more than once. If that argument is a scalar subquery or a nondeterministic or expensive function, it may run more than once.

ISNULL evaluates its first argument once. So when the input is an expensive subquery, COALESCE may do the work more than once while ISNULL does it once. This shows up in the execution plan as more than one evaluation of the subquery, and under READ COMMITTED two evaluations can return different values if the data changes between them. The example is illustrative: MAX returns NULL over an empty or all-NULL set, so the fallback is meaningful, and the subquery is only re-evaluated when its first evaluation is non-NULL.

There are two other differences. COALESCE takes any number of arguments and returns the first non-NULL, so it covers a fall-through across several columns that ISNULL, with its two arguments, cannot express in one call. And COALESCE is ANSI standard SQL while ISNULL is specific to T-SQL, so COALESCE ports to other database engines and ISNULL does not.

I reach for COALESCE by default, for the standard syntax, the multi-argument fall-through, and the result length that accounts for all its string arguments rather than truncating to the first. I choose ISNULL on purpose when I want one of its specific behaviors: a single evaluation of a costly first argument, NOT NULL result metadata for a computed column or constraint, or a result type pinned to the first argument. Choosing on type, nullability, and evaluation beats choosing by habit, and COALESCE type precedence can introduce an implicit conversion of its own, so it is not automatically the safe pick.

The reason the swap is easy to make is that both functions read as “give me this, or that if the first is NULL”, and for two short arguments of the same type they return the same value. The truncation needs a narrow first argument, the nullability difference only shows in metadata and DDL, and the repeated evaluation only shows in the plan. None of the three appears in a quick test with two string literals.

Next up is Part 8, NULL in unique keys and joins, where a single-column UNIQUE constraint allows one NULL but rejects a second, and an inner equi-join drops the rows whose key is NULL.

If you have been caught by an ISNULL truncation or a surprise second evaluation, I would like to hear about it. You can find me on Bluesky and LinkedIn.

References

1. Deciding between COALESCE and ISNULL in SQL Server - MSSQLTips, Aaron Bertrand. Compares the two functions on data-type precedence, silent truncation, result nullability, and argument count. ↩
2. ISNULL (Transact-SQL) - Microsoft Learn. Documents that ISNULL takes the data type and length of its first argument. ↩
3. COALESCE (Transact-SQL) - Microsoft Learn. Documents COALESCE, its CASE expansion, and data-type precedence across all arguments. ↩