🗄️ The COALESCE Default That Got Cut Down to Size
COALESCE has to pick a single data type and length for its result from among all the expressions handed to it, and SQL Server’s data type precedence rules decide that, not whichever argument looks like the intended fallback. When a narrow, explicitly-typed column (a VARCHAR(5), say) is mixed with a longer literal meant purely as a default, the result can end up governed by the narrower column’s declared length, and the longer literal gets implicitly cast down to fit it – truncated exactly the way it would be if you’d assigned it straight into that same narrow column.
🔎 The Problem
DECLARE @t TABLE (Code VARCHAR(5) NULL); INSERT INTO @t VALUES (NULL); SELECT COALESCE(Code, 'NOT-SET-YET') AS Resolved FROM @t; -- Expected: 'NOT-SET-YET' -- Actual: 'NOT-S' -- COALESCE's result type here takes VARCHAR(5) - Code's declared -- length - as the governing type, so the literal gets implicitly -- cast down to VARCHAR(5) and silently truncated to fit it.
✅ Fix: Explicitly Widen the Fallback Before Handing It to COALESCE
- Explicitly CAST or CONVERT the fallback literal, or the column, to a deliberately wide-enough type before passing it to COALESCE – for example COALESCE(Code, CAST(‘NOT-SET-YET’ AS VARCHAR(50))) – so the result’s length is one you chose on purpose, not whichever one SQL Server’s data type precedence rules happened to settle on.
- Treat any COALESCE or ISNULL call that mixes a narrow column with a wider literal or variable as something worth checking explicitly, especially after a column’s declared length was ever reduced – the call site itself never changes, but its silent truncation behavior does the moment the column gets narrower.
- Test with a default value genuinely longer than the narrowest input, not just a short placeholder during development – a two-character placeholder like ‘NA’ never reveals a five-character column’s truncation limit the way a realistic-length fallback value actually would.
⚠️ Why This Is Easy to Miss
- No error, warning, or truncation message is raised anywhere – SQL Server performs the implicit conversion and the resulting cast-to-length truncation exactly as silently as it would for a normal assignment into a too-narrow column.
- The bug stays invisible until the fallback branch is actually exercised with a genuinely long value, so a query tested only against rows where the column already holds a value – the common case – never shows any sign that the NULL-handling branch is broken at all.
COALESCE doesn’t ask which input is right – it asks which input’s type wins, and the loser gets cut to fit.

