Improve performance searching nvarchar values on varchar column

Question

I have a table with order data in an azure sql database. Our web developers (using django) consistently have queries that look like...

SELECT col_one, col_two from order_headers where order_number = N'X101010'

So the order_number will never, I hope, need to be nvarchar. As you have probably already guessed, the indexes are kinda useless as sql server will convert the order_number column to nvarchar for comparison. So do I...

1. Tell them to not do that anymore
2. Convert order_number to nvarchar (which wouldn't be that big a deal as it's a data mart and doesn't have the full set of orders)
3. Something else. Change collation to UTF-8, or something I'm not thinking of.

This is just one example. There are lots of queries across a bunch of tables that cause problems exactly like this. So a global or standard change would be ideal.

Execution plans.
With N'' (nvarchar value) https://www.brentozar.com/pastetheplan/?id=HJsc2DLGY

IO Stats

Table 'orders_details'. Scan count 9, logical reads 1656636, physical reads 16, page server reads 0, read-ahead reads 1694475, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Without N'(varchar value) https://www.brentozar.com/pastetheplan/?id=ry5ITwIMt

IO Stats

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'orders_details'. Scan count 1, logical reads 25, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Answer 1

How about making the columns Unicode and turn on Data Compression, specifically row compression. That has close to none overhead, but you get Unicode compression with it. I.e., you wouldn't pay much for having it an nvarchar compared to varchar thanks to Unicode compression that comes with row compression.

You could of course also consider the more aggressive page compression (which includes row compression and with that Unicode compression), but now you get into the "is it worth the overhead" discussion.

I very much doubt that UTF-8 play into this, since it only applies to varchar, so you won't be changing the data type (you'll just change the encoding on disk by choosing an UTF-8 collation). I.e., you would still get those non-seeks.

Here's good reading on the topic: https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/