C# strings silently kill your SQL Server indexes in Dapper

Original Article ↗ Hacker News Discussion ↗

Developers are dissecting a performance pitfall where C# ORMs like Dapper send `nvarchar` parameters to SQL Server columns defined as `varchar`, forcing implicit conversions that disable index seeks and cause large slowdowns. The root issues span SQL Server’s type precedence rules, legacy reliance on UTF‑16 (`nvarchar`) vs. ASCII/UTF‑8 (`varchar`), and trade-offs between Unicode support, storage size, and query optimizer behavior. Participants also debate schema design choices (enums vs. code tables, natural vs. surrogate keys), the value of stored procedures and type mapping as mitigations, and whether the blog post describing the problem shows telltale signs of uncritical LLM authorship.

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.