ISNULL vs COALESCE: the compiler's receipts, the benchmark, and the differences that actually bite

“Use COALESCE, it’s standard.” “Use ISNULL, it’s faster.” Every SQL Server team has both camps, both arguments are decades old, and both are usually made without evidence. Let’s settle the performance half with a benchmark — and then look at the semantic differences, which are the part that actually causes production bugs.

First, the receipts: they are not the same thing

You can ask SQL Server what it really compiled — the scalar expressions are right there in the query plan. Written as COALESCE(sn50, s2) and ISNULL(sn50, s2), the compiled forms are:

COALESCE  →  CASE WHEN [t].[sn50] IS NOT NULL THEN [t].[sn50] ELSE [t].[s2] END
ISNULL    →  isnull([t].[sn50], [t].[s2])

COALESCE doesn’t exist at execution time at all — the parser rewrites it into a CASE expression (that’s also exactly how the ANSI standard defines it). ISNULL survives as a native engine intrinsic. One is grammar, the other is a function. That difference shows up in both the timings and the semantics below.

The benchmark

Same setup as the rest of this series: each expression evaluated once per row over a 200,000-row scan, minimum of eight interleaved passes, reported as ns/row net of the scan baseline (full methodology here). I varied how often the first argument is actually NULL (never / 50% / always), since a CASE evaluates lazily and I wanted to catch any branch effects:

First arg is NULL…	ISNULL	COALESCE
never (0%)	19.8 ns/row	35.7 ns/row
half the rows (50%)	20.0 ns/row	38.7 ns/row
always (100%)	17.9 ns/row	34.1 ns/row
4-argument chain	46.3 ns/row	81.6 ns/row

Two findings. First — and against my own pre-registered hypothesis of “they’ll tie” — ISNULL is consistently about twice as fast per evaluation: the intrinsic does one NULL check and a copy, while the expanded CASE drags more general machinery through every row. NULL density barely matters for either. Second, chains widen the gap: COALESCE(a, b, c, d) expands to nested CASE WHEN a IS NOT NULL… arms, and at four arguments it costs ~75% more than the equivalent nested ISNULL.

And now the sentence that keeps this honest: twice as fast means ~18 nanoseconds saved per row — about 4 ms per million rows. There is exactly one situation where that’s a real argument: expressions evaluated across hundreds of millions of rows in ETL or reporting scans. Everywhere else, this is not a performance decision. So what should decide it? The semantics — and they have teeth.

The four differences that cause actual bugs

1. ISNULL truncates to the first argument’s type. The classic:

DECLARE @code CHAR(2) = NULL;
SELECT ISNULL(@code, 'UNKNOWN'),    -- 'UN'      (silently truncated!)
       COALESCE(@code, 'UNKNOWN');  -- 'UNKNOWN'

ISNULL returns the first argument’s type — your fallback gets cut to fit it. COALESCE returns the highest-precedence type among all arguments. If the fallback can be wider than the column, ISNULL is quietly eating data.

2. Type precedence can bite COALESCE instead. The same rule in reverse: COALESCE(varchar_col, 0) makes the result an INT (higher precedence), so every non-NULL string gets converted — and 'N/A' becomes a runtime conversion error. Mixed-type arguments deserve an explicit CAST no matter which function you use.

3. Nullability of the result. ISNULL(col, 0) is provably non-NULL, and the engine knows it — that matters in computed columns, SELECT INTO target types, and filtered logic. COALESCE(col, 0) is still considered nullable in several of those contexts. If you’re materializing a column, this difference is structural.

4. COALESCE can evaluate a subquery twice. Because COALESCE((subquery), x) expands to CASE WHEN (subquery) IS NOT NULL THEN (subquery) ELSE x END, the subquery can run twice per evaluation — once for the test, once for the value. With a nondeterministic or expensive subquery that’s both a performance and a correctness hazard. ISNULL evaluates its first argument once.

(And one in COALESCE’s favor: it’s ANSI standard, portable, and takes any number of arguments — ISNULL is SQL Server dialect, two arguments, nest it yourself.)

Verdict

• Choose on semantics, not speed. Fallback wider than the column → COALESCE. Need a provably non-NULL result (computed columns, SELECT INTO) → ISNULL. Subquery as the first argument → ISNULL. Portability or many arguments → COALESCE.
• When semantics don’t disambiguate — two same-typed columns, like the benchmark — it genuinely doesn’t matter below a few million rows. At ETL scale, the intrinsic is measurably cheaper, and chains amplify it.
• Whatever you pick, the type rules (1) and (2) are where the production incidents live. The performance question is the least interesting thing about these two functions — it just happens to finally have numbers.

---

Part of a series of measured answers to small T-SQL questions: + vs CONCAT, IIF vs CASE, SET vs SELECT vs DECLARE, temp-table cleanup. Methodology + harness: how to benchmark a microsecond.