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

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

#101

Post by wvenable » Sat, Mar 07, 2026, 6:58 PM UTC

downsplat wrote: post #96

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…

It's not really a Linux vs MS thing though. When Unicode first came out, it was 16-bit, so all the early adopters went with that. That includes Java, Windows, JavaScript, the ICU lintaries, LibreOffice and its predecessors, .NET, the C language (remember wchar_t?), and probably a few more. Utf8 turned out to be the better approach, and it's slowly taking over, but it was not only Linu/Unix that pushed it ahead, the e…

Database systems are inherently conservative -- once you add something you have to support it forever. Microsoft went hog wild on XML in the database and I haven't seen it used in over a decade now.

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

#102

Post by wvenable » Sat, Mar 07, 2026, 7:02 PM UTC

kstrauser wrote: post #66

wvenable wrote: post #59

Earlier quoted context omitted.

I don't think it was clear at the time that UTF-8 would take off. UCS-2 and then UTF-16 was well established by 2000 in both Microsoft technologies and elsewhere (like Java). Linux, despite the existence of UTF-8, would still take years to get acceptable internationalization support. Developing good and secure internationalization is a hard problem -- it took a long time for everyone. It's now 2026, everything always…

I don’t remember it quite that way. Localization was a giant question, sure. Are we using C or UTF-8 for the default locale? That had lots of screaming matches. But in the network service world, I don’t remember ever hearing more than a token resistance against choosing UTF-8 as the successor to ASCII. It was a huge win, especially since ASCII text is already valid UTF-8 text. Make your browser default to parsing doc…

Microsoft did the hard work of supporting Unicode when UTF-8 didn't exist (and mostly when UTF-16 didn't exist).

Any system that continued with only ASCII well into the 2000s could mostly just jump into UTF-8 without issue. Doing nothing for non-English users for almost two decades turned out to be a solid plan long term. Microsoft certainly didn't have that option.

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

#103

Post by wvenable » Sat, Mar 07, 2026, 7:06 PM UTC

SigmundA wrote: post #87

wvenable wrote: post #65

Earlier quoted context omitted.

Running the optimizer for every execution of the same query is... not very optimal.

It can run it for a range of values: https://learn.microsoft.com/en-us/sql/relational-databases/p... Also the simpler and maybe better approach is just make the decision every time as an operation in the plan, attempt the cast if it fails then scan and cast a many times the other way, if it succeeds then use the index, this isn't hard and adds one extra cast attempt on the slow path otherwise it does what everyone ha…

I'm not sure it makes sense to add more checks and another operation to every single query just for the case where the user explicitly mislabels the types. You're going to slow down everything everywhere (slightly) for a pretty obscure case. I suspect, in the long term, this would be a bad choice.

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

#104

Post by sgarland » Sat, Mar 07, 2026, 7:27 PM UTC

andy81 wrote: post #84

wvenable wrote: post #55

Earlier quoted context omitted.

It's way better to just use a DBMS that supports enums. I know SQL server isn't one of those but I still don't store my coded values as strings.

The way to do enums in SQL (generally, not just MSSQL) is another table. It's better that they don't offer several ways to do the same thing.

While I generally would prefer lookup tables, it's much easier to sell dev teams on "it looks and acts like a string - you don't have to change anything."

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

#105

Post by elmigranto » Sat, Mar 07, 2026, 7:38 PM UTC

DeathMetal3000 wrote: post #100

elmigranto wrote: post #77

Third party dependencies are very easy: you just have to intimately know how it is implemented in addition to knowing your own code and stack, and then you are golden! Nothing to learn, just focus on making your app, it’s all taken care of by This One Simple Package ;) These things are so far from free as our tooling presents with “just nuget it or whatever”.

I’m sure writing their own ORM would have given them instantaneous insight into this issue and introduced no other challenges. Open source developers hate this one weird trick!

Especially for things used directly, you need to understand both, own and third party code, roughly to the same level. With own code, you only care for your own use case; with third-party — you have to kind of get everyone else's.

Depending on what you do and the dependency's scope, either way can make sense.

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

#106

Post by wvenable » Sat, Mar 07, 2026, 8:25 PM UTC

SigmundA wrote: post #86

wvenable wrote: post #55

Earlier quoted context omitted.

It's way better to just use a DBMS that supports enums. I know SQL server isn't one of those but I still don't store my coded values as strings.

How do you store them? Also enums are not user configurable normally. It would be a good feature to have them, but they don't work well in many cases. Typical code tables with code, description and anything else needed for that value which the user can configure in the app. Sure you can use integers instead of codes, now all your results look like 1, 2, 3, 4 for all your coded columns when trying to debug or write ad…

Enums are for non user-configurable values.

For configurable values, obviously you use a table. But those should have an auto-integer primary key and if you need the description, join for it.

Ints are by far more the efficient way to store and query these values -- the length of the string is stored as an int and variable length values really complicate storage and access. If you think strings save space or time that is not right.

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

#107

Post by SigmundA » Sat, Mar 07, 2026, 9:03 PM UTC

wvenable wrote: post #106

SigmundA wrote: post #86

Earlier quoted context omitted.

How do you store them? Also enums are not user configurable normally. It would be a good feature to have them, but they don't work well in many cases. Typical code tables with code, description and anything else needed for that value which the user can configure in the app. Sure you can use integers instead of codes, now all your results look like 1, 2, 3, 4 for all your coded columns when trying to debug or write ad…

Enums are for non user-configurable values. For configurable values, obviously you use a table. But those should have an auto-integer primary key and if you need the description, join for it. Ints are by far more the efficient way to store and query these values -- the length of the string is stored as an int and variable length values really complicate storage and access. If you think strings save space or time that…

>Enums are for non user-configurable values

In the systems I work with most coded values are user configurable.

>But those should have an auto-integer primary key and if you need the description, join for it.

Not ergonomic now when querying data or debugging things like postal state are 11 instead of 'NY'

select * from addresses where state = 11, no thanks.

Your whole results set becomes a bunch of ints that can be easily transposed causing silly errors. Of course I have seen systems that use guids to avoid collision, boy is that fun, just use varchar or char if your penny pinching and ok with fixed sizes.

>the length of the string is stored as an int

No it's stored as a smallint 2 bytes. So a single character code is 3 bytes rather than a 4 byte int. 2 chars is the same as an int. They do not complicate storage access in any meaningful way.

You could use smallint or tinyint for your primary key and I could use char(2) and char(1) and get readable codes if I wanted to really save space.

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

#108

Post by SigmundA » Sat, Mar 07, 2026, 9:18 PM UTC

wvenable wrote: post #103

SigmundA wrote: post #87

Earlier quoted context omitted.

It can run it for a range of values: https://learn.microsoft.com/en-us/sql/relational-databases/p... Also the simpler and maybe better approach is just make the decision every time as an operation in the plan, attempt the cast if it fails then scan and cast a many times the other way, if it succeeds then use the index, this isn't hard and adds one extra cast attempt on the slow path otherwise it does what everyone ha…

I'm not sure it makes sense to add more checks and another operation to every single query just for the case where the user explicitly mislabels the types. You're going to slow down everything everywhere (slightly) for a pretty obscure case. I suspect, in the long term, this would be a bad choice.

The check is added if it sees a varchar column and nvarchar parameter predicate on it.

It currently just does a scan in that situation which orders of magnitude more expensive with a cast for every row vs a single extra cast check on the single parameter value that may avoid all those other casts in a common situation.

There is no planning overhead, it's already detecting the situation. The execution overhead is a single extra cast on top of the cast per row, so n+1 vs n with the potential to eliminate n with a very common charset.

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

#109

Post by simonask » Sat, Mar 07, 2026, 11:56 PM UTC

zabzonk wrote: post #88

procaryote wrote: post #85

Earlier quoted context omitted.

It was a mess back then though. Unicode fixed that.

I'm not convinced that Unicode fixed anything. I was kind of hoping, way back when, that everyone would adopt ASCII, as a step to a more united world. But things seem to have got more differentiated, and made things much more difficult.

For whom? Certainly not any of the humans trying to use the computer.

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

#110

Post by simonask » Sat, Mar 07, 2026, 11:59 PM UTC

SigmundA wrote: post #107

wvenable wrote: post #106

Earlier quoted context omitted.

Enums are for non user-configurable values. For configurable values, obviously you use a table. But those should have an auto-integer primary key and if you need the description, join for it. Ints are by far more the efficient way to store and query these values -- the length of the string is stored as an int and variable length values really complicate storage and access. If you think strings save space or time that…

>Enums are for non user-configurable values In the systems I work with most coded values are user configurable. >But those should have an auto-integer primary key and if you need the description, join for it. Not ergonomic now when querying data or debugging things like postal state are 11 instead of 'NY' select * from addresses where state = 11, no thanks. Your whole results set becomes a bunch of ints that can be e…

Please take literally one course.

Do NOT use mnemonics as primary keys. It WILL bite you.