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

31–40 of 131 posts

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

#31

Post by NegativeLatency »

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.

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

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

#32

Post by ziml77 »

briHass wrote: post #25

I've found and fixed this bug before. There are 2 other ways to handle it Dapper has a static configuration for things like TypeMappers, and you can change the default mapping for string to use varchar with: Dapper.SqlMapper.AddTypeMap(typeof(string),System.Data.DbType.AnsiString). I typically set that in the app startup, because I avoid NVARCHAR almost entirely (to save the extra byte per character, since I rarely n…

Sprocs are how I handle complex queries rather than embedding them in our server applications. It's definitely saved me from running into problems like this. And it comes with another advantage of giving DBAs more control to manage performance (DBAs do not like hearing that they can't take care of a performance issue that's cropped up because the query is compiled into an application)

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

#33

Post by wvenable »

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

Blame the Unicode consortium for not coming up UTF-8 first (or, really, at all). And for assuming that 65526 code points would be enough for everyone.

So many problems could be solved with a time machine.

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

#34

Post by psidebot »

SigmundA wrote: post #16
wvenable wrote: post #6

Earlier quoted context omitted.

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

Some examples of coded fields that may be known to be ascii: order name, department code, business title, cost center, location id, preferred language, account type…

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

#36

Post by diath »

It's weird that the article does not show any benchmarks but crappy descriptions like "milliseconds to microseconds" and "tens of thousands to single digits". This is the kind of vague performance description LLMs like to give when you ask them about performance differences between solutions and don't explicitly ask for a benchmark suite.

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

#37

Post by kstrauser »

wvenable wrote: post #33
paulsutter wrote: post #30

Earlier quoted context omitted.

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

Blame the Unicode consortium for not coming up UTF-8 first (or, really, at all). And for assuming that 65526 code points would be enough for everyone. So many problems could be solved with a time machine.

The first draft of Unicode was in 1988. Thompson and Pike came up with UTF-8 in 1992, made an RFC in 1998. UTF-16 came along in 1996, made an RFC in 2000.

The time machine would've involved Microsoft saying "it's clear now that USC-2 was a bad idea, so let's start migrating to something genuinely better".

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

#38

Post by mvdtnz »

This is a really interesting blog post - the kind of old school stuff the web used to be riddled with. I must say - would it have been that hard to just write this by hand? The AI adds nothing here but the same annoying old AI-isms that distract from the piece.

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

#40

Post by recursive »

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…

In 92 it was a conference talk. In 98 it was adopted by the IETF. Point probably stands though.
Post reply on HN