C# strings silently kill your SQL Server indexes in Dapper

Original Article ↗ Hacker News Discussion ↗

Root issue: nvarchar vs varchar and implicit conversions

  • Many note this is not “a C# problem” but a classic SQL Server nvarchar vs varchar / collation issue that affects many ORMs.
  • When a query compares an nvarchar parameter with a varchar indexed column, SQL Server implicitly converts the column to nvarchar, making the predicate non-sargable and killing index seeks.
  • Some see this as user error/type mismatch; others view it as a practical optimizer deficiency.

Storage, performance, and encoding trade-offs

  • varchar is smaller (N+2 bytes) than nvarchar (2N+2) on SQL Server; more rows per page and smaller indexes can matter for performance.
  • Some argue most user-facing text should be Unicode; others say many coded / system fields are safely ASCII.
  • Several suggest using UTF-8 collations with varchar (introduced in SQL Server 2019) as the modern default, though some mention early issues and lingering skepticism.

Optimizer behavior: bug or by design?

  • One side: optimizer follows type precedence (promoting varchar to nvarchar) and must avoid narrowing conversions that could lose data; this is “by design.”
  • Other side: optimizer could cheaply inspect parameter values or add a conditional cast fast-path for ASCII-only nvarchar, falling back to scans otherwise. Critics call current behavior suboptimal but acknowledge correctness constraints.

Workarounds and practices

  • Match types between parameters and columns; explicitly specify DbType.AnsiString in Dapper, or default its string mapping to varchar.
  • Use stored procedures with strongly typed parameters to avoid repeated implicit conversions.
  • Some wrap ORMs behind internal interfaces to centralize such fixes.

Natural keys, enums, and coded values

  • Debate over storing coded values as human-readable strings vs integer IDs.
  • Pro-string camp values readability and easier debugging; pro-int camp stresses efficiency, fixed-width storage, and avoiding mnemonic/natural keys that may change.

Meta: article quality and LLM concerns

  • Multiple commenters feel the blog post’s prose and vague performance claims resemble unedited LLM output and doubt its rigor.
  • Others defend the post as a useful real-world find, arguing strict benchmarks aren’t necessary when the mechanism is well explained.