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

81–90 of 131 posts

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

#81

Post by rmunn »

downsplat wrote: post #74

Did this post come out of a freezer from 1998? Who on earth creates databases in Latin1 in 2026? Nevermind, looks like Sql Server didn't add utf8 collations until 2019 (!) and for decades people had to choose column by column between the 16-bit overhead of "nvarchar" and latin1... And still do if they want a bit of backwards compatibility. Amazing.

"Just use Postgres" (which defaults to UTF-8 encoding unless specifically configured to use something else) is looking like better and better advice every day.

Doesn't help those tied to legacy systems that would require a huge, expensive effort to upgrade, though. Sorry, folks. There's a better system, you know it's a better system, and you can't use it because switching is too expensive? I've been there (not databases, in my case) and it truly sucks.

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

#82

Post by bonesss »

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…

As a general issue of hygiene I tend to wrap any ORM and access it through an internal interface.

1) The joy of writing and saying DapperWrapper can’t be overstated.

2) in conjunction with meaningful domain types it lets you handle these issues across the app at a single point of control, and capture more domain semantics for testing.

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

#83

Post by rmunn »

pllbnk wrote: post #71
_vertigo wrote: post #61

Earlier quoted context omitted.

> No schema changes. No new indexes. No query rewrites. Just telling Dapper the correct parameter type.

Are we automatically discarding everything that might or might not have been written or assisted by an LLM? I get it when the articles are the type of meaningless self improvement or similar kind of word soup. However, if hypothetically an author uses LLM assistance to improve their styling to their liking, I see nothing wrong with that as long as the core message stands out.

I've seen so many LLM-generated articles by this point that obviously had no human editing done beforehand — just prompt and slap it onto the Web — that it makes me wonder every time. If I read this article, will I actually learn only truth? Or are there some key parts of this article that are actually false because the LLM hallucinated them, and the human involved didn't bother to double-check the article before publishing it?

If someone was just using the LLM for style, that's fine. But if they were using it for content, I just can't trust that it's accurate. And the time cost for me to read the article just isn't worth it if there's a chance it's wrong in important ways, so when I see obvious signs of LLM use, I just skip and move on.

Now, if someone acknowledged their LLM use up front and said "only used for style, facts have been verified by a human" or whatever, then I'd have enough confidence in the article to spend the time to read it. But unacknowledged LLM use? Too great a risk of uncorrected hallucinations, in my experience, so I'll skip it.

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

#84

Post by andy81 »

wvenable wrote: post #55
SigmundA wrote: post #42

Earlier quoted context omitted.

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.

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.

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

#85

Post by procaryote »

zabzonk wrote: post #54
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.

> Unicode is a requirement everywhere human language is used Strange then how it was not a requirement for many, many years.

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

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

#86

Post by SigmundA »

wvenable wrote: post #55
SigmundA wrote: post #42

Earlier quoted context omitted.

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.

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-hoc stuff. Also ints are not variable length so your wasting space for short codes and you have to know ahead time if its only going to be 1,2,4 or 8 bytes.

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

#87

Post by SigmundA »

wvenable wrote: post #65
jstrong wrote: post #62

Earlier quoted context omitted.

optimizer can't inspect the value? pretty dumb optimizer, then.

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 has to do manually in their code like this article but transparently.

The adaptive join operator does something much more complex: https://learn.microsoft.com/en-us/sql/relational-databases/p...

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

#88

Post by zabzonk »

procaryote wrote: post #85
zabzonk wrote: post #54

Earlier quoted context omitted.

> Unicode is a requirement everywhere human language is used Strange then how it was not a requirement for many, many years.

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.

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

#89

Post by SigmundA »

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.

Mostly agree separate tables can have multiple attributes besides a text description and can be exposed for modification to the application easily so users or administrators can add and modify codes.

A common extra attribute for a coded value is something for deprecation / soft delete, so that it can be marked as no longer valid for future data but existing data can remain with that code, also date ranges its valid for etc, also parent child code relationships.

Enums would be a good feature but they have a much more limited use case for static values you know ahead of time that will have no other attributes and values cannot be removed even if never used or old data migrated to new values.

Common real world codes like US postal state can take advantage of there being agreed upon codes such as 'NY' and 'New York'.

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

#90

Post by downsplat »

gfody wrote: post #56
dspillett wrote: post #26

Earlier quoted context omitted.

> I'm not sure why anyone would choose varchar for a column in 2026 The same string takes roughly half the storage space, meaning more rows per page and therefore a smaller working set needed in memory for the same queries and less IO. Also, any indexes on those columns will also be similarly smaller. So if you are storing things that you know won't break out of the standard ASCII set⁰, stick with [VAR]CHARs¹, otherw…

utf16 is more efficient if you have non-english text, utf8 wastes space with long escape sequences. but the real reason to always use nvarchar is that it remains sargeable when varchar parameters are implicitly cast to nvarchar.

The old defense of 16-bit chars, popping up in 2026 still! Utf8 is efficient enough for all general purpose uses.

If you're storing gigabytes of non-latin-alphabet text, and your systems are constrained enough that it makes a difference, 16-bit is always there. But I'd still recommend anyone starting a system today to not worry and use utf8 for everything.j

Post reply on HN