COALESCE vs ISNULL in SQL Server: the differences that matter

Both replace a NULL with something else:

SELECT ISNULL(NULL, 'fallback') AS with_isnull, COALESCE(NULL, NULL, 'fallback') AS with_coalesce;

ISNULL(a, b) is SQL Server’s own function and takes exactly two arguments. COALESCE(a, b, c, ...) is standard SQL, takes any number, and returns the first that isn’t NULL.

Most of the time, they’re interchangeable. The differences show up in four places, each of which has caused real bugs. The examples run on SQL Server 2022, and the last one uses the SpDevDemo database.

1. The return type

ISNULL returns the type of its first argument. COALESCE returns the type with the highest precedence among all its arguments, like a CASE expression would.

DECLARE @code VARCHAR(3) = NULL, @qty INT = NULL;

SELECT
    ISNULL(@code, 'abcdef')   AS isnull_text,
    COALESCE(@code, 'abcdef') AS coalesce_text,
    ISNULL(@qty, 2.7)         AS isnull_number,
    COALESCE(@qty, 2.7)       AS coalesce_number;

isnull_text  coalesce_text  isnull_number  coalesce_number
-----------  -------------  -------------  ---------------
abc          abcdef         2              2.7

ISNULL quietly truncated the replacement to VARCHAR(3) and converted 2.7 to an INT, because that’s the type of the first argument. No error, no warning. COALESCE returned the full string and the decimal.

This is the difference most likely to cause a bug: an ISNULL(@shortColumn, 'a longer default') that cuts off the default.

2. Nullability of the result

When you create a table from a query (SELECT ... INTO, or a computed column, or a view used by an indexed view), SQL Server decides whether each column can be NULL:

CREATE TABLE #source (v INT NULL);
INSERT #source VALUES (NULL), (5);

SELECT ISNULL(v, 0) AS with_isnull, COALESCE(v, 0) AS with_coalesce
INTO #result
FROM #source;

SELECT name, is_nullable
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#result');

name           is_nullable
-------------  -----------
with_isnull    0
with_coalesce  1

ISNULL with a non-NULL replacement is known to never return NULL, so the column is NOT NULL. COALESCE is treated as a CASE expression, whose result SQL Server considers nullable. This matters for computed columns you want to use in a primary key, and for columns created with SELECT ... INTO.

3. Subqueries are evaluated twice by COALESCE

COALESCE(x, y) is shorthand for CASE WHEN x IS NOT NULL THEN x ELSE y END, and SQL Server really does expand it that way. If x is a subquery, it appears twice in the expanded expression and can be run twice:

SET STATISTICS IO ON;

SELECT SUM(x.Spent) AS Total
FROM (
    SELECT COALESCE((SELECT SUM(o.Total) FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId), 0) AS Spent
    FROM dbo.Customers AS c
    WHERE c.CustomerId <= 100
) AS x;

SELECT SUM(x.Spent) AS Total
FROM (
    SELECT ISNULL((SELECT SUM(o.Total) FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId), 0) AS Spent
    FROM dbo.Customers AS c
    WHERE c.CustomerId <= 100
) AS x;

SET STATISTICS IO OFF;

-- COALESCE
Table 'Worktable'. Scan count 100, logical reads 532596
Table 'Orders'. Scan count 10, logical reads 2745
-- ISNULL
Table 'Orders'. Scan count 1, logical reads 1340

Same answer. The COALESCE version read dbo.Orders twice as much and added half a million worktable reads, because its plan computes the subquery once for the IS NOT NULL test and again for the value. Beyond the cost, a subquery evaluated twice can give two different answers under concurrent changes, so COALESCE((SELECT ...), 0) can, in rare cases, return NULL.

With a subquery, use ISNULL, or move the subquery into a join or CROSS APPLY and apply COALESCE to the column.

4. Number of arguments

ISNULL takes two. For “the first non-NULL of these four”, COALESCE(MobilePhone, WorkPhone, HomePhone, 'none') is much clearer than nested ISNULL calls.

Summary

	ISNULL(a, b)	COALESCE(a, b, ...)
Arguments	Exactly 2	2 or more
Standard SQL	No (SQL Server only)	Yes
Result type	Type of a: may truncate or convert b	Highest-precedence type of all arguments
Result nullable	No, if b isn’t NULL	Yes
Subquery as first argument	Evaluated once	Can be evaluated twice

A reasonable rule: use COALESCE by default for its type behavior and portability, and ISNULL when the first argument is a subquery or when you need a NOT NULL result.

Related functions

• NULLIF(a, b) returns NULL when a = b, otherwise a. Its classic use is avoiding division by zero: Total / NULLIF(Quantity, 0).
• IIF(condition, a, b) is shorthand for a two-branch CASE.
• Never compare with = NULL: it’s never true. Use IS NULL, or IS [NOT] DISTINCT FROM on SQL Server 2022 to compare two values treating NULLs as equal.