C# strings silently kill your SQL Server indexes in Dapper

#1

Post by PretzelFisch » Fri, Mar 06, 2026, 10:55 PM UTC

C# strings silently kill your SQL Server indexes in Dapper
consultwithgriff.com

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

#2

Post by wvenable » Fri, Mar 06, 2026, 11:24 PM UTC

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.

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

#3

Post by beart » Fri, Mar 06, 2026, 11:31 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.

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.

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

#4

Post by jiggawatts » Fri, Mar 06, 2026, 11:32 PM UTC

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.

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

#5

Post by _3u10 » Fri, Mar 06, 2026, 11:35 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.

Generally if it stores user input it needs to support Unicode. That said UTF-8 is probably a way better choice than UTF-16/UCS-2

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

#6

Post by wvenable » Fri, Mar 06, 2026, 11:37 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.

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

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

#7

Post by wvenable » Fri, Mar 06, 2026, 11:41 PM 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.

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

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

#8

Post by enord » Fri, Mar 06, 2026, 11:46 PM UTC

This is due to utf-16, an unforgivable abomination.

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

#9

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

wvenable wrote: post #7

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.

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.

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

#10

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

_3u10 wrote: post #5

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.

Generally if it stores user input it needs to support Unicode. That said UTF-8 is probably a way better choice than UTF-16/UCS-2

UTF-8 is a relatively new thing in MSSQL and had lots of issues initially, I agree it's better and should have been implemented in the product long ago.

I have avoided it and have not followed if the issues are fully resolved, I would hope they are.