Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a COALESCE Default Value That Gets Silently Truncated Because the Result Took the First Column’s Declared Length

- 11.10.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

SQL Server: Fix a Blocking Chain That Isn't a Deadlock and Never Resolves on Its Own

SQL Server: Fix a Blocking Chain That Isn't a Deadlock and Never Resolves on Its Own

SQL Server: Querying JSON Columns Like a Pro

SQL Server: Querying JSON Columns Like a Pro

The Hidden Killer: Scalar Functions — How to Avoid Massive Slowdowns

The Hidden Killer: Scalar Functions — How to Avoid Massive Slowdowns

Views: 7

Post navigation

WordPress: Fix a WP_Query meta_query That Silently Returns the Wrong Posts Because Multiple Clauses Default to AND Without a Relation Key
Visual Studio: Fix a Hot Reload Edit That Silently Does Nothing Because Changing a Method’s Signature Is an Unsupported Edit

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com