Performance killing default settings for
@SQLServer! Since 2019, it has had UTF-8 support on their VARCHAR columns, if you use a collation that ends in _UTF8. Great, as in finally. And their JDBC driver have the flag 'sendStringParametersAsUnicode' set to 'true' by default, so you can send parameters to the db using 'statement.setString(1, "🍻")'. Good. But here's the INSANE part: That setting turns all String parameters from the JDBC client into NVARCHAR on the wire, which the server receives. But that results in conversion problems, and now you won't hit indices on VARCHAR columns! 🤯 That is, all Strings will be transferred as NVARCHAR, while you might be using VARCHAR on everything on your tables (afterall, they support Unicode!). This can have a devastating performance hit for many operations! You can turn that feature to 'false', and then be limited to single byte character sets (so forget about that Unicode🍻 in a String). If you now instead use stmt.setNString(..) to send that parameter as NVARCHAR, you can send Unicode - you'll still trash indices, but now only for that particular parameter. The only way to get this right, is to use the crappy NVARCHAR for columns, and use the even more crappy stmt.setNString(..) on the client - special casing both the SDL and the Java code for Microsoft SQL Server. NVARCHAR uses UTF-16/UCS-2, while VARCHAR with _UTF8 collation uses UTF-8 - but there is no way to actually employ that without trashing performance! Here's a bug exploring this problem: github.com/microsoft/mssq… Here's a follow-up bug to that, suggesting to set that default to 'false' (which also is really bad, since you now can't use Unicode) - closed "as designed": github.com/microsoft/mssq… Here's a bug from
@lukasederfrom jOOQ that started this: github.com/jOOQ/jOOQ/issu… And it seems like the actual SQL Server engine folks don't give a toot about this. How hard could it be to send the strings as unicode so as to be able to actually store them in those UTF-8 VARCHAR columns, without the massive conversion hit that sending them as NVARCHAR evidently gives? Such an insane default, with such insane performance hitting consequences, without anyone of the Microsoft employees doing anything about it?? Just🤯!