COALESCE() vs ISNULL() in T-SQL

Introduction

Every data engineer knows this ghost story: a query that worked perfectly for months suddenly collapses after a small schema change or a new data feed. Nothing obvious breaks—until you notice truncated strings, missing rows, or a slow bleed of bad metrics. Then comes the long hunt through layers of transformations, views, and stored procedures to find the cause.

Often, the culprit is a quiet choice made early on: COALESCE() vs ISNULL(). They look interchangeable—but they’re not. Understanding the difference can save hours of debugging and days of downtime.

---

TL;DR

• Use ISNULL(expr, replacement) for two arguments, when you want to preserve the first argument’s type and length and get a NOT NULL result in metadata.
• Use COALESCE(a, b, c, …) for multiple fallbacks, ANSI compliance, and to avoid string truncation that happens with ISNULL.
• The traps: type coercion, string truncation, repeated evaluation, and misleading nullability. These bugs often appear long after deployment.

---

What they are

• Use ISNULL(expr, replacement) for two arguments when you want to preserve the first argument’s type and length and get a NOT NULL result in metadata.
• Use COALESCE(a, b, c, …) for multiple fallbacks, ANSI compliance, and to avoid string truncation that happens with ISNULL.

---

Key differences

Data type & length

ISNULL keeps the type and length of its first argument. COALESCE follows type precedence rules across all arguments.

DECLARE @s VARCHAR(3) = NULL;
SELECT ISNULL(@s, 'ABCDEF');   -- 'ABC' (truncated)SELECT COALESCE(@s, 'ABCDEF'); -- 'ABCDEF' (correct)

Lesson: If you use ISNULL, cast your first argument to the size you need.

Expression evaluation

COALESCE may evaluate an expression more than once; ISNULL won’t.

SELECT ISNULL(NULL, GETDATE());   -- evaluated onceSELECT COALESCE(NULL, GETDATE()); -- may evaluate twice

This matters when using non-deterministic or expensive expressions.

Nullability metadata

ISNULL can mark results as NOT NULL when the replacement is non-nullable. COALESCE is conservative—its result is usually nullable. That can affect computed columns, constraints, or downstream tools.

Sargability

Both can block index seeks:

WHERE ISNULL(Col, 0) = 0   -- non-sargable-- Better:WHERE Col = 0 OR Col IS NULL

Normalize nulls earlier or use computed columns for performance.

---

When to use which

Use ISNULL(expr, replacement)

• Only two arguments are needed.
• You want to preserve the column’s original type/length.
• You need consistent metadata marked NOT NULL.
• You want predictable single evaluation.

Use COALESCE(a, b, c, …)

• Multiple fallbacks are needed.
• You want portable, ANSI-standard code.
• You need to avoid string truncation.
• You’re fine with conservative nullability.

---

Common hidden bugs

Silent truncation

SELECT ISNULL(Varchar10Col, 'Longer string');
-- Works fine... until 'Longer string' is longer than 10 chars.

Fix: Use COALESCE or cast explicitly.

Type coercion

COALESCE(IntCol, DecimalCol)  -- becomes DECIMAL via precedence

Fix: Cast explicitly to the desired type.

Double evaluation

COALESCE may re-evaluate non-deterministic expressions. Use ISNULL or precompute values.

Metadata mismatches

Tools might infer nullability from metadata, not behavior. Use ISNULL if NOT NULL status matters.

Sargability loss

Avoid both in WHERE clauses. Use explicit conditions or computed columns instead.

---

Doing It Yourself

Before typing, clarify intent:

• Need multiple fallbacks? → COALESCE
• Need to preserve data type and length? → ISNULL
• Appears in a WHERE or JOIN predicate? → Avoid both.
• Metadata must be NOT NULL? → ISNULL

When in doubt, cast explicitly and test edge cases—short strings, large decimals, null-heavy data. Prevent silent truncation before it happens.

---

Using GenAI

When AI writes SQL, remember—it doesn’t know your schema or data design. It guesses based on patterns.

Without context, AI will usually:

• Pick COALESCE for general SQL or multi-fallback cases.
• Pick ISNULL for SQL Server or when the prompt explicitly says so.

To steer it correctly, include context in your prompt:

“SQL Server 2019, must preserve data type length.” → expects ISNULL “Cross-platform query, multiple fallbacks.” → expects COALESCE

AI isn’t wrong; it’s context-blind. Treat it like a smart junior developer—fast, eager, and overconfident. Tell it what environment you’re in, what you care about, and always verify its output.

---

Quick reference

---

Final takeaway

ISNULL is predictable but provincial. COALESCE is portable but quirky. The wrong one won’t fail immediately—it’ll quietly corrupt your data later. Cast explicitly, test both, and when using AI, specify your intent like your data depends on it—because it does.