10 ms·
C# strings silently kill your SQL Server indexes in Dapper
- wvenable 7mo agoThis 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.
- beart 7mo agoI 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.
- _3u10 7mo agoGenerally 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
- SigmundA 7mo agoUTF-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.
- kstrauser 7mo ago> 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 considering it the new thing.
- recursive 7mo agoIn 92 it was a conference talk. In 98 it was adopted by the IETF. Point probably stands though.
- swasheck 7mo agothe 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.
- SigmundA 7mo agoUTF-8 was introduced in SQL Server 2019: https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2019?view=sql-server-ver17#unicode-support https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-...
- swasheck 7mo agothanks. now i see the point that the poster was making.
- da_chicken 7mo agoI 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.
- downsplat 7mo agoIt'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.
- Dwedit 7mo agoThe 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...
- wvenable 7mo ago> 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.
- SigmundA 7mo agoVast 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.
- simonask 7mo agoEnglish 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.
- NegativeLatency 7mo agoAlso less awkward to make it right the first time, instead of explaining why someone can’t type their name or an emoji
- SigmundA 7mo agoSpecifically not talking about a name field
- SigmundA 7mo agoI 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.
- SigmundA 7mo agoTo complicate matters SQL Server can do Nvarchar compression, but they should have just done UTF-8 long ago: https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/unicode-compression-implementation?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/d... Also UTF-8 is actually just a varchar collation so you don't use nvarchar with that, lol?
- SigmundA 7mo agoYes I have run into this regardless of client language and I consider it a defect in the optimizer.
- wvenable 7mo agoI 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.
- SigmundA 7mo agoCan 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.
- wvenable 7mo agoFor a literal/parameter that happens to be ASCII, a person might know it would fit in varchar, but the optimizer has to choose a plan that stays correct in the general case, not just for that one runtime value. By telling SQL server the parameter is a nvarchar value, you're the one telling it that might not be ASCII.
- applfanboysbgon 7mo agoI think this is a rather pertinent showcase of the danger of outsourcing your thinking to LLMs. This article strongly indicates to me that it is LLM-written, and it's likely the LLM diagnosed the issue as being a C# issue. When you don't understand the systems you're building with, all you can do is take the plausible-sounding generated text about what went wrong for granted, and then I suppose regurgitate it on your LLM-generated portfolio website in an ostensible show of your profound architectural knowledge.
- cosmez 7mo agoThis is a common issue, and most developers I worked with are not aware of it until they see the performance issues. Most people are not aware of how Dapper maps types under the hood; once you know, you start being careful about it. Nothing to do with LLMs, just plain old learning through mistakes.
- keithnz 7mo agoactually, LLMs do way better, with dapper the LLM generates code to specify types for strings
- ziml77 7mo agoThis is not at all just an LLM thing. I've been working with C# and MS SQL Server for many years and never even considered this could be happening when I use Dapper. There's likely code I have deployed running suboptimally because of this. And it's not like I don't care about performance. If I see a small query taking more than a fraction of a second when testing in SSMS or If I see a larger query taking more than a few seconds I will dig into the query plan and try to make changes to improve it. For code that I took from testing in SSMS and moved into a Dapper query, I wouldn't have noticed performance issues from that move if the slowdown was never particularly large.
- dspillett 7mo ago> 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¹, otherwise use N[VAR]CHARs. Of course if you can guarantee that your stuff will be used on recent enough SQL Server versions that are configured to support UTF8 collations, then default to that instead unless you expect data in a character set where that might increase the data size over UTF16. You'll get the same size benefit for pure ASCII without losing wider character set support. Furthermore, if you are using row or page compression it doesn't really matter: your wide-character strings will effectively be UTF8 encoded anyway. But be aware that there is a CPU hit for processing compressed rows and pages every access because they remain compressed in memory as well as on-disk. -------- [0] Codes with fixed ranges, etc. [1] Some would say that the other way around, and “use NVARCHAR if you think there might be any non-ASCIII characters”, but defaulting to NVARCHAR and moving to VARCHAR only if you are confident is the safer approach IMO.
- gfody 7mo agoutf16 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.
- exceptione 7mo agoWhat do you mean with non-english text? I don't think "Ä" will be more efficient in utf16 than in utf8. Or do you mean utf16 wins in cases of non-latin scripts with variable width? I always had the impression that utf8 wins on the vast majority of symbols, and that in case of very complex variable width char sets it depends on the wideness if utf16 can accommodate it. On a tangent, I wonder if emoji's would fit that bill too..
- Tuna-Fish 7mo ago
- paulsutter 7mo agoUtf8 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
- wvenable 7mo agoBlame 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.
- kstrauser 7mo agoThe 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".
- gpvos 7mo agoMS could easily have added proper UTF-8 support in the early 2000s instead of the late 2010s.
- kstrauser 7mo agoYep. It would've been a better landing pad than UTF-16 since they had to migrate off UCS-2 anyway.
- wvenable 7mo agoI 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 looks different in hindsight.
- croes 7mo agoSince MS SQL Server 2019 varchar supports unicode so now it’s the opposite, you use nvarchar instead of varchar for backwards compatibility reasons.
- jklowden 7mo agoI’m not sure why the top-rated reply begins by presuming anything about the problem domain. Many domains have a specified language and implied if not explicit collation. Rejecting characters outside that domain is part of the job. There are no emojis listed on the NASDAQ.
- jiggawatts 7mo agoThis feels like a bug in the SQL query optimizer rather than Dapper. It ought to be smart enough to convert a constant parameter to the target column type in a predicate constraint and then check for the availability of a covering index.
- wvenable 7mo agoIt's the optimizer caching the query plan as a parameterized query. It's not re-planning the index lookup on every execution.
- SigmundA 7mo agoThe parameter type is part of the cache identity, nvarchar and varchar would have two cache entries with possibly different plans.
- deleted 7mo ago[deleted]
- valiant55 7mo agoThere's a data type precedence that it uses to determine which value should be casted[0]. Nvarchar is higher precedence, therefore the varchar value is "lifted" to an nvarchar value first. This wouldn't be an issue if the types were reversed. 0: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-...
- Sankozi 7mo agoSo such issues can appear in more products and more datatypes (int and bigint have same problem). This is really bad rule for SQL's "equality" operator. Still optimizer should be able to handle it - if the result is the same, optimizer should take faster path.
- beart 7mo ago
- enord 7mo agoThis is due to utf-16, an unforgivable abomination.
- adzm 7mo agoeven better is Entity Framework and how it handles null strings by creating some strange predicates in SQL that end up being unable to seek into string indexes
- smithkl42 7mo agoBeen bit by that before: it's not just an issue with Dapper, it can also hit you with Entity Framework.
- andrelaszlo 7mo agoI thought, having just read the title, that maybe it's time to upgrade if you're still on Ubuntu 6.06.
- briHass 7mo agoI'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 need anything outside of ANSI.) Or, one could use stored procedures. Assuming you take in a parameter that is the correct type for your indexed predicate, the conversion happens once when the SPROC is called, not done by the optimizer in the query. I still have mixed feelings about overuse of SQL stored procedures, but this is a classic example of where on of their benefits is revealed: they are a defined interface for the database, where DB-specific types can be handled instead of polluting your code with specifics about your DB. (This is also a problem for other type mismatches like DateTime/Date, numeric types, etc.)
- ziml77 7mo agoSprocs 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)
- bonesss 7mo agoAs 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.
- maciekkmrk 7mo agoInteresting problem, but the AI prose makes me not want to read to the end.
- diath 7mo agoIt'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.
- _vertigo 7mo ago> No schema changes. No new indexes. No query rewrites. Just telling Dapper the correct parameter type.
- pllbnk 7mo agoAre 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.
- rmunn 7mo agoI'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.
- mvdtnz 7mo agoThis 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.
- ltbarcly3 7mo agoLife is too short to use SQL Server. I know people that use it will swear it's "not bad anymore" but yes it is.
- bni 7mo agoyes it is
- pjmlp 7mo agoI never had this issue with Dapper, as others point out, an holding it wrong problem.
- downsplat 7mo agoDid 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.
- rmunn 7mo ago"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.
- elmigranto 7mo agoThird 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”.
- DeathMetal3000 7mo agoI’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!
- elmigranto 7mo agoEspecially 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.
- bunbun69 7mo agoAI slop article Also no meaningful benchmarking was done
- wronex 7mo agoThis makes me sad. We have these type safe languages, then a DB comes along and brakes the type barrier. What are we to do? Property attributes and an ORM? Is there a Linq to SQL thing?
- jklowden 7mo ago> SQL Server has to convert every single value in the column to nvarchar before it can compare. This of course is not true. It is a defect in Microsoft’s query planner. And the proof lies in the remedy. The recommended solution is to convert the search argument type to match that of the index. The user is forced to discover the problem and adjust manually. SQL Server could just as well have done that automatically. No information is lost converting nvarchar to varchar if the index is varchar. If the search argument is ‘’, no conversion from varchar will match it (unless the index data is UTF8, which the server should know). This is a longstanding bug in SQLserver, and not the only one. Instead of patting ourselves on the back for avoiding what SQL Server “has to do”, we should be insisting it not do it. Anymore.
- mergisi 7mo ago[dead]