How significant is the performance difference when

问题:

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.

回答1:

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.