I understand that join on nvarchar is slower because index is bigger as nvarchar using 2 bytes for each character but int is 4 bytes all the time. Is the join performance difference significant? Is there any strong reason to avoid join on nvarchar? I couldn't find any MSDN article about the topic.
Asked
Active
Viewed 2,743 times
10
-
I expect the performance difference to be negligible, but you should really try it out for your specific case. – Sjoerd Jun 26 '11 at 12:23
-
2@Sjoerd: over a few dozen rows, yes. Otherwise it *will* be huge – gbn Jun 26 '11 at 12:43
1 Answers
12
At least 8x CPU. This is the measurable increase in comparing nvarchar over varchar: unicode sorting and comparison rules are more complex that straight varchar.
- What are the main performance differences between varchar and nvarchar SQL Server data types?
- SQL Server uses high CPU when searching inside nvarchar strings
So, assuming varchar and int are equal (they aren't) nvarchar will have overhead compared to int
Then, byte for byte ('1234' vs 1234) you're comparing 10 bytes vs 4 bytes. This also means a wider key for less index and data entries per page = more IO.
Finally, if your nvarchar is more then 450 characters, you can't index it because index key is max 900 bytes wide.
