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...
- Tell them to not do that anymore
- 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)
- 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.