加入 nvarchar 与加入 int 时的性能差异有多大 [英] How significant is the performance difference when joining on nvarchar versus on int
问题描述
我知道 nvarchar 上的连接速度较慢，因为索引比 nvarchar 大，每个字符使用 2 个字节，但 int 始终为 4 个字节.连接性能差异是否显着?是否有充分的理由避免加入 nvarchar?我找不到有关该主题的任何 MSDN 文章.
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.
推荐答案
至少 8x CPU.这是 nvarchar 与 varchar 比较的可测量增加:unicode 排序和比较规则比直接 varchar 更复杂.
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.
因此，假设 varchar 和 int 相等(它们不是)与 int 相比，nvarchar 将有开销
So, assuming varchar and int are equal (they aren't) nvarchar will have overhead compared to int
然后，逐字节('1234' 与 1234)比较 10 字节与 4 字节.这也意味着每页索引和数据条目更少的更宽的键 = 更多的 IO.
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.
最后，如果您的 nvarchar 超过 450 个字符，则无法对其进行索引，因为索引键的宽度最大为 900 个字节.
Finally, if your nvarchar is more then 450 characters, you can't index it because index key is max 900 bytes wide.
这篇关于加入 nvarchar 与加入 int 时的性能差异有多大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
