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

91–100 of 131 posts

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

#91

Post by SigmundA »

kstrauser wrote: post #47
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.

Those are all single byte characters in UTF-8.

We are talking nvarchar here, yes UTF-8 solves this issue completely and MSSQL supports it now days with varchar.

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

#92

Post by SigmundA »

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 non sargeablilty is an optimizer deficiency IMO. It could attempt to cast just like this article is doing manually in code, if that success use index, if it fails scan and cast a million times the other way in a scan.

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

#93

Post by gfody »

divingdragon wrote: post #80
Tuna-Fish wrote: post #76

Earlier quoted context omitted.

Japanese, Chinese, Korean and Indic scripts are mostly 2 bytes per character on UTF-16 and mostly 3 bytes per character in UTF-8.

Really, as an East Asian language user the rest of the comments here make me want to scream.

hn often makes me want to scream

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

#94

Post by gfody »

downsplat wrote: post #90
gfody wrote: post #56

Earlier quoted context omitted.

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

it certainly isn't the best choice for sql server, see: https://sqlquantumleap.com/2018/09/28/native-utf-8-support-i...

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

#95

Post by gfody »

SigmundA wrote: post #92
gfody wrote: post #56

Earlier quoted context omitted.

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 non sargeablilty is an optimizer deficiency IMO. It could attempt to cast just like this article is doing manually in code, if that success use index, if it fails scan and cast a million times the other way in a scan.

implicit casts should only widen to avoid quiet information loss, if the optimizer behaved as you suggest the query could return incorrect results and potentially more than expected, with even worse consequences

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

#96

Post by downsplat »

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…

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 entire networking world did, especially http. Props also to early perl for jumping straight to utf8.

Still... Utf8's superiority was clear enough by 2005 or so, MS could and should have seen it by then instead of waiting until 2019 to add utf8 collations to its database. Funny to see Sql Server falling behind good old Mysql on such a basic feature.

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

#98

Post by SigmundA »

gfody wrote: post #95
SigmundA wrote: post #92

Earlier quoted context omitted.

The non sargeablilty is an optimizer deficiency IMO. It could attempt to cast just like this article is doing manually in code, if that success use index, if it fails scan and cast a million times the other way in a scan.

implicit casts should only widen to avoid quiet information loss, if the optimizer behaved as you suggest the query could return incorrect results and potentially more than expected, with even worse consequences

It should not return incorrect results, if the nvarchar only contains ascii it will cast perfectly, if it doesn't then do the slow scan path, it's a simple check and the same work its doing for every row in the current behavior except one time and more restricted. Can you give me an example of an incorrect result here?

I am not talking about the default cast behavior from nvarchar to varchar, but a specific narrow check the optimizer can use to make decision in the plan of ascii or not with no information loss because it will do the same thing as before if it does not pass the one time parameter check.

By far the most common cause of this situation is using ascii only in a nvarchar because like say in this example the client language is using an nvarchar equivalent for all strings, which is pretty much universal now days and that is the default conversion when using a sql client library, one must remember to explicitly cast rather than the db doing it for you which is the expected behavior and the source of much confusion.

This would be purely an optimization fast path check otherwise fall back to the current slow path, correct results always with much faster results if only ascii is present in the string.

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

#99

Post by exceptione »

divingdragon wrote: post #80
Tuna-Fish wrote: post #76

Earlier quoted context omitted.

Japanese, Chinese, Korean and Indic scripts are mostly 2 bytes per character on UTF-16 and mostly 3 bytes per character in UTF-8.

Really, as an East Asian language user the rest of the comments here make me want to scream.

I am not sure if you mean me, as I just asked a question. I wonder what the best way is to handle this disparity for international software. It seems like either you punish the Latin alphabets, or the others.

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

#100

Post by DeathMetal3000 »

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!
Post reply on HN