March 6, 2026
Unicode Wars: Indexes vs Strings
C# Strings Silently Kill Your SQL Server Indexes in Dapper
Dev fight erupts: tiny string tweak turns fast searches into full-table crawls
TLDR: A tiny type mismatch—Unicode strings sent to a non‑Unicode column—makes SQL Server skip your index and crawl, but you can fix it by sending varchar. Commenters split between blaming the optimizer, cursing UTF‑16, and wondering why varchar still exists; it matters because this can melt CPUs.
A developer found their database melting down because a simple search was secretly doing the worst thing possible: reading every row, every time. The culprit? C# strings sent as Unicode via Dapper, while the column expected plain old non‑Unicode text. That mismatch forces SQL Server to convert everything before comparing, which disables the index (the shortcut) and triggers a full scan (the slog). Translation: your fast search turns into a treadmill, your CPU screams, and your users wait.
The crowd went full popcorn. One camp, led by wvenable, yelled “this isn’t C# at all” and roasted anyone still using non‑Unicode text: “why choose varchar in 2026?” Another camp, with jiggawatts, pointed the finger at Microsoft’s query optimizer: if the engine’s so smart, why can’t it fix this on its own? Meanwhile, enord dropped the spicy meme: “utf‑16 is an unforgivable abomination,” dunking on how Windows world strings got us here. Entity Framework took stray fire too, with folks grumbling it mangles null strings into weird filters that also dodge indexes.
The fix? Almost embarrassingly simple: tell Dapper your parameter is varchar, not nvarchar, and match the size to your column. Two lines, instant speed. But the comments turned it into a culture war: Team Unicode vs Team Legacy, optimizers vs ORMs, and one invisible gotcha that made everyone check their execution plans.
Key Points
- •Dapper passes C# strings as nvarchar(4000) by default via ADO.NET, which can mismatch varchar columns.
- •SQL Server performs CONVERT_IMPLICIT when comparing nvarchar parameters to varchar columns, preventing index seeks and causing scans.
- •This mismatch can dramatically increase logical reads and CPU usage, even for simple queries on indexed columns.
- •Severity depends on collation: common defaults like SQL_Latin1_General_CP1_CI_AS produce full scans; other collations may still incur overhead.
- •The fix is to explicitly send varchar parameters (DbType.AnsiString) with an appropriate size using DynamicParameters or Dapper’s DbString.