C# strings silently kill your SQL Server indexes in Dapper • Hacker News Board

Re: C# strings silently kill your SQL Server indexes in Dapper

#11

Post by SigmundA » Fri, Mar 06, 2026, 11:52 PM UTC

beart wrote: post #3

wvenable wrote: post #2

This really doesn't have anything to do with C#. This is your classic nvarchar vs varchar issue (or unicode vs ASCII). The same thing happens if you mix collations. I'm not sure why anyone would choose varchar for a column in 2026 unless if you have some sort of ancient backwards compatibility situation.

I agree with your first point. I've seen this same issue crop up in several other ORMs. As to your second point. VARCHAR uses N + 2 bytes where as NVARCHAR uses N*2 + 2 bytes for storage (at least on SQL Server). The vast majority of character fields in databases I've worked with do not need to store unicode values.

To complicate matters SQL Server can do Nvarchar compression, but they should have just done UTF-8 long ago:

https://learn.microsoft.com/en-us/sql/relational-databases/d...

Also UTF-8 is actually just a varchar collation so you don't use nvarchar with that, lol?

Re: C# strings silently kill your SQL Server indexes in Dapper

#12

Post by [deleted] » Fri, Mar 06, 2026, 11:53 PM UTC

SigmundA wrote: post #9

wvenable wrote: post #7

Earlier quoted context omitted.

It's the optimizer caching the query plan as a parameterized query. It's not re-planning the index lookup on every execution.

The parameter type is part of the cache identity, nvarchar and varchar would have two cache entries with possibly different plans.

[deleted]

Re: C# strings silently kill your SQL Server indexes in Dapper

#13

Post by adzm » Fri, Mar 06, 2026, 11:54 PM UTC

even better is Entity Framework and how it handles null strings by creating some strange predicates in SQL that end up being unable to seek into string indexes

Re: C# strings silently kill your SQL Server indexes in Dapper

#14

Post by smithkl42 » Fri, Mar 06, 2026, 11:56 PM UTC

Been bit by that before: it's not just an issue with Dapper, it can also hit you with Entity Framework.

Re: C# strings silently kill your SQL Server indexes in Dapper

#15

Post by SigmundA » Fri, Mar 06, 2026, 11:57 PM UTC

wvenable wrote: post #2

This really doesn't have anything to do with C#. This is your classic nvarchar vs varchar issue (or unicode vs ASCII). The same thing happens if you mix collations. I'm not sure why anyone would choose varchar for a column in 2026 unless if you have some sort of ancient backwards compatibility situation.

Yes I have run into this regardless of client language and I consider it a defect in the optimizer.

Re: C# strings silently kill your SQL Server indexes in Dapper

#16

Post by SigmundA » Fri, Mar 06, 2026, 11:59 PM UTC

wvenable wrote: post #6

beart wrote: post #3

Earlier quoted context omitted.

I agree with your first point. I've seen this same issue crop up in several other ORMs. As to your second point. VARCHAR uses N + 2 bytes where as NVARCHAR uses N*2 + 2 bytes for storage (at least on SQL Server). The vast majority of character fields in databases I've worked with do not need to store unicode values.

> The vast majority of character fields in databases I've worked with do not need to store unicode values. This has not been my experience at all. Exactly the opposite, in fact. ASCII is dead.

Vast majority of text fields I see are coded values that are perfectly fine using ascii, but I deal mostly with English language systems.

Text fields that users can type into directly especially multiline tend to need unicode but they are far fewer.

Re: C# strings silently kill your SQL Server indexes in Dapper

#17

Post by wvenable » Sat, Mar 07, 2026, 12:02 AM UTC

SigmundA wrote: post #15

wvenable wrote: post #2

This really doesn't have anything to do with C#. This is your classic nvarchar vs varchar issue (or unicode vs ASCII). The same thing happens if you mix collations. I'm not sure why anyone would choose varchar for a column in 2026 unless if you have some sort of ancient backwards compatibility situation.

Yes I have run into this regardless of client language and I consider it a defect in the optimizer.

I wouldn't consider it a defect in the optimizer; it's doing exactly what it's told to do. It cannot convert an nvarchar to varchar -- that's a narrowing conversion. All it can do is convert the other way and lose the ability to use the index. If you think that there is no danger converting an nvarchar that contains only ASCII to varchar then I have about 70+ different collations that say otherwise.

Re: C# strings silently kill your SQL Server indexes in Dapper

#18

Post by valiant55 » Sat, Mar 07, 2026, 12:07 AM UTC

jiggawatts wrote: post #4

This feels like a bug in the SQL query optimizer rather than Dapper. It ought to be smart enough to convert a constant parameter to the target column type in a predicate constraint and then check for the availability of a covering index.

There's a data type precedence that it uses to determine which value should be casted[0]. Nvarchar is higher precedence, therefore the varchar value is "lifted" to an nvarchar value first. This wouldn't be an issue if the types were reversed.

0: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-...

Re: C# strings silently kill your SQL Server indexes in Dapper

#19

Post by applfanboysbgon » Sat, Mar 07, 2026, 12:08 AM UTC

wvenable wrote: post #2

This really doesn't have anything to do with C#. This is your classic nvarchar vs varchar issue (or unicode vs ASCII). The same thing happens if you mix collations. I'm not sure why anyone would choose varchar for a column in 2026 unless if you have some sort of ancient backwards compatibility situation.

I think this is a rather pertinent showcase of the danger of outsourcing your thinking to LLMs. This article strongly indicates to me that it is LLM-written, and it's likely the LLM diagnosed the issue as being a C# issue. When you don't understand the systems you're building with, all you can do is take the plausible-sounding generated text about what went wrong for granted, and then I suppose regurgitate it on your LLM-generated portfolio website in an ostensible show of your profound architectural knowledge.

Re: C# strings silently kill your SQL Server indexes in Dapper

#20

Post by beart » Sat, Mar 07, 2026, 12:19 AM UTC

jiggawatts wrote: post #4

This feels like a bug in the SQL query optimizer rather than Dapper. It ought to be smart enough to convert a constant parameter to the target column type in a predicate constraint and then check for the availability of a covering index.

How do you safely convert a 2 byte character to a 1 byte character?