ISNULL Versus COALESCE: Return Length Can Change

ISNULL versus COALESCE can change a replacement string’s return length even when both expressions handle the same missing input. I check the expression type before trusting the displayed value. A longer fallback can be narrowed before any later assignment.

Give each input an explicit string type

The example defines FirstText as varchar(3) and ReplacementText as varchar(10) in every row. Those types are intentional. An untyped NULL literal would introduce a different inference question.

The first row combines a missing narrow value with the longer text Longer. ISNULL returns the typed first expression’s varchar(3) contract. Its expected replacement result is Lon.

COALESCE combines the supplied expressions under its type-selection rules. Here both expressions belong to the varchar family with different lengths. Its expected return width accommodates the longer input, preserving Longer.

I expose the original inputs beside both outputs. That identifies where characters were lost. A single output string could otherwise suggest that the fallback itself was already short.

WITH Inputs AS
(
    SELECT CaseId,FirstText,ReplacementText
    FROM (VALUES
        (1,CAST(NULL AS varchar(3)),CAST('Longer' AS varchar(10))),
        (2,CAST('AB' AS varchar(3)),CAST('Longer' AS varchar(10))),
        (3,CAST('' AS varchar(3)),CAST('Longer' AS varchar(10))),
        (4,CAST(NULL AS varchar(3)),CAST(NULL AS varchar(10)))
    ) AS v(CaseId,FirstText,ReplacementText)
), Resolved AS
(
    SELECT *,ISNULL(FirstText,ReplacementText) AS IsnullText,
        COALESCE(FirstText,ReplacementText) AS CoalesceText
    FROM Inputs
)
SELECT CaseId,FirstText,ReplacementText,IsnullText,CoalesceText,
    CAST(SQL_VARIANT_PROPERTY(CAST(IsnullText AS sql_variant),'MaxLength') AS int) AS IsnullMaximumBytes,
    CAST(SQL_VARIANT_PROPERTY(CAST(CoalesceText AS sql_variant),'MaxLength') AS int) AS CoalesceMaximumBytes
FROM Resolved
ORDER BY CaseId;

Inspect expression capacity rather than current text length

The two MaximumBytes columns inspect the result expressions through SQL_VARIANT_PROPERTY. For present results, the expected capacities are three and ten. They describe underlying type capacity rather than the number of characters currently stored.

At the second row, both outputs contain AB. Their expected capacities still differ. Matching displayed text therefore does not prove that the two expression contracts are identical.

At the empty-string row, both outputs remain empty and their capacities remain three and ten. Empty text is a present value. Neither expression chooses the replacement solely because the first string has zero characters.

The final row supplies two typed missing values. Both results remain NULL and their value-based property calls return NULL. That row does not claim a different compile-time expression type.

Make the desired width part of the source expression

If the fallback must retain its entire text, choose the intended return type before replacement. Widening the first ISNULL argument can express that policy. Casting the already truncated result afterward cannot recover discarded characters.

A narrow return type can also be deliberate. The application might require a bounded field with separately validated inputs. In that case, test the fallback against the same length limit rather than relying on silent narrowing.

Compare both missing and present first values when choosing a replacement type. A filled first value does not expose fallback truncation. A fallback-only example hides that matching text can still have different capacities.

The example uses ordinary ASCII letters in varchar values. It does not generalize byte capacity into a character guarantee for every encoding. Unicode and UTF-8 inputs deserve their own explicit type tests.

Keep this comparison within its demonstrated scope

ISNULL has a special return-type rule when its first argument is a literal NULL. This example intentionally avoids that case. Each missing input has an explicit varchar type.

COALESCE can accept multiple expressions and follows type precedence across them. ISNULL accepts a check expression and one replacement. This example narrows the comparison to two same-family strings.

Other differences include expression evaluation and inferred nullability. Pure constants do not prove concurrency behavior for subqueries. Do not use this length demonstration as evidence that every replacement pattern is interchangeable.

A replacement policy needs separate outcomes for missing text and empty text. A return-width policy also needs a longer fallback. Read those cases together before assigning the expression into a bounded field.

Widen the first argument when the whole fallback matters.

Matching text is not matching types, it is a result that can hide a different width.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.