COALESCE() vs ISNULL() in T-SQL

The short answer

For SQL Server 2022 and current Azure SQL:

• Use ISNULL(check_expression, replacement_value) when you need exactly two arguments and deliberately want the result to use the first argument’s data type.
• Use COALESCE(expression, ...n) when you need the first non-NULL value from two or more expressions, or when its type-precedence behavior is the one you want.
• Do not rely on either function to make type, length, nullability, or performance choices invisible. State the type you need with CAST or CONVERT, and test the query shape that will run in production.

Both functions replace NULL in ordinary query work. They differ in ways that matter most in computed columns, mixed-type expressions, and expressions with subqueries. Microsoft: ISNULL, Microsoft: COALESCE

The difference you can see first: result type and length

ISNULL returns the data type of its first argument. When that argument is a short string, SQL Server converts the replacement to that short type.

DECLARE @short_text varchar(3) = NULL;

SELECT ISNULL(@short_text, 'ABCDEF') AS IsNullResult;   -- ABC
SELECT COALESCE(@short_text, 'ABCDEF') AS CoalesceResult; -- ABCDEF

COALESCE follows the same data-type-precedence rules as a CASE expression. In this example, it returns a longer varchar value. That does not make COALESCE a universal cure for truncation: mixed expressions can still produce a type you did not intend. When the output contract matters, make it explicit.

SELECT COALESCE(
    CAST(@short_text AS varchar(50)),
    CAST('ABCDEF' AS varchar(50))
) AS ExplicitResult;

SQL Server converts the lower-precedence type to the higher-precedence type when expressions differ. Microsoft data-type precedence

The differences that matter later

Number of fallbacks

ISNULL takes two arguments. COALESCE takes two or more, so it fits a genuine fallback chain.

SELECT COALESCE(PreferredName, LegalName, AccountName, 'Unknown') AS DisplayName
FROM dbo.Customer;

Do not use a longer fallback chain merely because it is available. Each fallback should represent a business decision the reader can explain.

Evaluation

SQL Server documents COALESCE as a CASE shorthand. Its inputs can be evaluated more than once; a subquery can therefore return different values between evaluations under some isolation levels. ISNULL, as a function, is evaluated once. For an expensive or nondeterministic expression, precompute it in a subquery or APPLY, or use ISNULL when its type behavior is appropriate.

SELECT ISNULL(x.NullableValue, 1)
FROM (
    SELECT (SELECT Nullable FROM dbo.Demo WHERE SomeCol = 1) AS NullableValue
) AS x;

This is not a reason to avoid COALESCE in ordinary column expressions. It is a reason to notice when an input is more than a simple value.

Nullability metadata

ISNULL and COALESCE can expose different nullability metadata even when they return the same value at runtime. That matters for computed columns, keys, and indexed expressions. Test the actual expression in the DDL you intend to ship; do not infer its eligibility from a query result alone.

Predicates: keep the column visible

Wrapping a column in either function can make a predicate harder for SQL Server to optimize. When the intended rule is “zero or NULL,” write that rule directly and inspect the actual execution plan for the real table and indexes.

-- Often easier for the optimizer to reason about than ISNULL(Col, 0) = 0
WHERE Col = 0
   OR Col IS NULL;

There is no universal rewrite for every predicate. Selectivity, indexes, and the surrounding query still decide the plan.

A practical decision guide

Situation	Prefer	Reason
Exactly two values and first-argument type is intentional	ISNULL	Its return-type rule is explicit.
First non-NULL value from several candidates	COALESCE	It accepts a fallback chain.
Output type or length is a contract	Explicit CAST plus either function	Make the contract visible.
Subquery or nondeterministic input	Precompute, then choose	Avoid surprises from repeated evaluation.
Computed key or indexed expression	Test the DDL	Nullability metadata can differ.
WHERE or JOIN predicate	Write the NULL rule directly and inspect the plan	Avoid hiding the column inside a function without measuring.

Using an AI assistant for this choice

An assistant cannot infer your schema, data distribution, or downstream metadata requirements from a function name. Give it the platform and the constraint:

“SQL Server 2022/current Azure SQL. The output must be varchar(50), the expression appears in a computed column, and the fallback comes from a subquery. Show the explicit casts and explain the nullability and evaluation tradeoffs.”

Then run a small reproduction with representative values, inspect metadata where it matters, and review the resulting plan for performance-sensitive predicates.

Final takeaway

ISNULL and COALESCE are not interchangeable style preferences. Choose the one whose behavior matches the business rule, make the output type explicit when it matters, and test the edge case that would be expensive to discover after release.