Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

C# strings silently kill your SQL Server indexes in Dapper

consultwithgriff.com

Post reply on HN
Print view View original

41–50 of 131 posts

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

#41

Post by SigmundA »

wvenable wrote: post #17
SigmundA wrote: post #15

Earlier quoted context omitted.

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.

Can you give an example whats dangerous about converting a nvarchar with only ascii (0-127) then using the index otherwise fallback to a scan?

If we simply went to UTF-8 collation using varchar then this wouldn't be an issue either, which is why you would use varchar in 2026, best of both worlds so to speak.

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

#42

Post by SigmundA »

simonask wrote: post #28
SigmundA wrote: post #16

Earlier quoted context omitted.

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.

English has plenty of Unicode — claiming otherwise is such a cliché… Unicode is a requirement everywhere human language is used, from Earth to the Boöotes Void.

I am talking about coded values, like Status = 'A', 'B' or 'C'

Taking double the space for this stuff is a waste of resources and nobody usually cares about extended characters here in English language systems at least they just want something more readable than integers when querying and debugging the data. End users will see longer descriptions joined from code tables or from app caches which can have unicode.

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

#43

Post by SigmundA »

NegativeLatency wrote: post #31
simonask wrote: post #28

Earlier quoted context omitted.

English has plenty of Unicode — claiming otherwise is such a cliché… Unicode is a requirement everywhere human language is used, from Earth to the Boöotes Void.

Also less awkward to make it right the first time, instead of explaining why someone can’t type their name or an emoji

Specifically not talking about a name field

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

#44

Post by swasheck »

kstrauser wrote: post #21
SigmundA wrote: post #10

Earlier quoted context omitted.

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.

> 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. Their insistence on making the rest of the world go along with their obsolete pet scheme would be annoying if I ever had to use their stuff for anything ever. UTF-8 was conceived in 1992, and here we are in 2026 with a reasonably popularly database still considerin…

the data types were introduced with SQL Server 7 (1998) so i’m not sure it’s accurate to state that it’s considered as the new thing.

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

#45

Post by SigmundA »

swasheck wrote: post #44
kstrauser wrote: post #21

Earlier quoted context omitted.

> 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. Their insistence on making the rest of the world go along with their obsolete pet scheme would be annoying if I ever had to use their stuff for anything ever. UTF-8 was conceived in 1992, and here we are in 2026 with a reasonably popularly database still considerin…

the data types were introduced with SQL Server 7 (1998) so i’m not sure it’s accurate to state that it’s considered as the new thing.

UTF-8 was introduced in SQL Server 2019:

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-...

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

#46

Post by da_chicken »

kstrauser wrote: post #21
SigmundA wrote: post #10

Earlier quoted context omitted.

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.

> 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. Their insistence on making the rest of the world go along with their obsolete pet scheme would be annoying if I ever had to use their stuff for anything ever. UTF-8 was conceived in 1992, and here we are in 2026 with a reasonably popularly database still considerin…

I would be more critical of Microsoft choosing to support UCS-2/UTF-16 if Microsoft hadn't completed their implementation of Unicode support in the 90s and then been pretty consistent with it.

Meanwhile Linux had a years long blowout in the early 2000s over switching to UTF-8 from Latin-1. And you can still encounter Linux programs that choke on UTF-8 text files or multi-byte characters 30 years later (`tr` being the one I can think of offhand). AFAIK, a shebang is still incompatible with a UTF-8 byte order mark. Yes, the UTF-8 BOM is both optional and unnecessary, but it's also explicitly allowed by the spec.

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

#47

Post by kstrauser »

SigmundA wrote: post #42
simonask wrote: post #28

Earlier quoted context omitted.

English has plenty of Unicode — claiming otherwise is such a cliché… Unicode is a requirement everywhere human language is used, from Earth to the Boöotes Void.

I am talking about coded values, like Status = 'A', 'B' or 'C' Taking double the space for this stuff is a waste of resources and nobody usually cares about extended characters here in English language systems at least they just want something more readable than integers when querying and debugging the data. End users will see longer descriptions joined from code tables or from app caches which can have unicode.

Those are all single byte characters in UTF-8.

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

#48

Post by Dwedit »

_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

The one place UTF-16 massively wins is text that would be two bytes as UTF-16, but three bytes as UTF-8. That's mainly Chinese, Japanese, Korean, etc...

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

#49

Post by Dwedit »

paulsutter wrote: post #30
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.

Utf8 solved this completely. It works with any length unicode and on average takes up almost as little storage as ascii. Utf16 is brain dead and an embarrassment

It gets worse for UTF-16, Windows will let you name files using unpaired surrogates, now you have a filename that exists on your disk that cannot be represented in UTF-8 (nor compliant UTF-16 for that matter). Because of that, there's yet another encoding called WTF-8 that can represent the arbitrary invalid 16-bit values.

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

#50

Post by Slothrop99 »

simonask wrote: post #28
SigmundA wrote: post #16

Earlier quoted context omitted.

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.

English has plenty of Unicode — claiming otherwise is such a cliché… Unicode is a requirement everywhere human language is used, from Earth to the Boöotes Void.

Just to be pedantic, those characters are in 'ANSI'/CP1252 and would be fine in a varchar on many systems.

Not that I disagree — Win32/C#/Java/etc have 16-bit characters, your entire system is already 'paying the price', so weird to get frugal here.

Post reply on HN