ISNULL Truncates Your Replacement Value; COALESCE Doesn’t
ISNULL and COALESCE look like the same function wearing different names. Both take a value, both give you something else when that value is NULL.
They differ in how they decide the type of what comes back, and that difference can shorten your data without raising anything.
Five characters
|
1 2 3 |
DECLARE @short varchar(5) = NULL; SELECT [result] = ISNULL(@short, 'truncated-value'); |
|
1 2 3 |
result ------ trunc |
truncated-value is fifteen characters. What came back is five. No error, no warning, no truncation message.
COALESCE on the same input:
|
1 |
SELECT [result] = COALESCE(@short, 'truncated-value'); |
|
1 2 3 |
result --------------- truncated-value |
I ran both on SQL Server 2019 (15.0.4480.2) and SQL Server 2025 (17.0.1125.2). Same answer on each.
Why
ISNULL takes the data type of its first argument and returns that.[1] The first argument here is varchar(5), so the result is varchar(5), and the replacement value is cut to fit.
COALESCE is a different mechanism. It is shorthand for a CASE expression, and its return type is determined by data type precedence across all its arguments.[2][3] With a varchar(5) and a fifteen-character literal, the result is varchar(15).
You can see the declared types without running either expression:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [system_type_name] = [d].[system_type_name] , [is_nullable] = [d].[is_nullable] FROM [sys].[dm_exec_describe_first_result_set] ( N'DECLARE @s varchar(5) = NULL; SELECT ISNULL(@s, ''truncated-value'');' , NULL , 0 ) AS [d]; |
| Type | ISNULL |
COALESCE |
|---|---|---|
system_type_name |
varchar(5) |
varchar(15) |
is_nullable |
0 | 1 |
The nullability column matters too
ISNULL reports its result as NOT NULL. COALESCE reports it as nullable.
That is not a rounding difference in the metadata. COALESCE can return NULL, because if every argument is NULL the result is NULL. ISNULL with a non-NULL replacement cannot, and SQL Server records that.
Where this shows up is computed columns and indexed views. A computed column defined with ISNULL can be marked NOT NULL, which lets you index it or make it part of a primary key. The same column defined with COALESCE is nullable and cannot be used the same way.
So there is one real case where ISNULL is the right call, and it is a schema case rather than a query case.
Where the truncation actually bites
A three-line repro leaves out the context that makes this hard to spot. The version that reaches production looks more like this:
|
1 2 3 4 5 |
SELECT [customer_id] = [c].[customer_id] , [display_name] = ISNULL([c].[preferred_name], [c].[legal_name]) FROM [dbo].[customer] AS [c]; |
If preferred_name is nvarchar(20) and legal_name is nvarchar(100), every customer without a preferred name gets their legal name cut to twenty characters. The query succeeds. The report renders. Someone notices months later that long names are clipped, and the cause is a function that was doing what it documents.
COALESCE in the same position returns nvarchar(100), because precedence picks the wider type.
The other differences
Two more differences.
COALESCE can evaluate an argument more than once. Because it expands to CASE, a subquery or non-deterministic function passed to COALESCE may be evaluated twice: once for the NULL test and again to produce the value. ISNULL evaluates its input once. If an argument is expensive or has side effects, that difference is real.
COALESCE is ANSI SQL, ISNULL is T-SQL only. Relevant if the code has to move to another engine, irrelevant otherwise.
The rule
Use COALESCE unless you specifically need the NOT NULL result for a computed column or an indexed view.
I default to it because the failure mode is harder to detect. A wrong type raises an error you fix in minutes; a shortened string raises nothing and becomes a data problem you find much later, if at all.
For the related question of comparing values that might be NULL rather than replacing them, IS DISTINCT FROM handles that case without the usual OR ... IS NULL pattern. And if you want to know how SQL Server records nullability on the page itself, that is in the NULL bitmap.
This is one of a series on the T-SQL conventions I actually use and why. Also in it: One NULL in the List and NOT IN Returns Nothing and Double-Hyphen Comments Can Comment Out Your WHERE Clause.
Have you been caught by an ISNULL truncation, and how long did it take to spot? Bluesky or LinkedIn.
References
- ISNULL (Transact-SQL) – Microsoft Learn. Documents that the return type is that of the first expression, and that the replacement value is implicitly converted to it. ↩
- COALESCE (Transact-SQL) – Microsoft Learn. Covers the CASE expansion, the data type precedence rule for the return type, and the repeated-evaluation caveat. ↩
- Data Type Precedence (Transact-SQL) – Microsoft Learn. The precedence order COALESCE uses to pick a result type across its arguments. ↩