SQL Server不区分大小写的排序规则 [英] SQL Server case insensitive collation
问题描述
在SQL Server中使用不区分大小写的排序规则（在查询性能方面）有什么好处/缺点？
我的数据库当前正在使用不区分大小写的排序规则，我不太喜欢。我非常想将其更改为区分大小写。更改排序规则时应该注意些什么？
（我将其添加为单独的答案，因为它与我的答案有很大不同首先。）
好吧，找到了一些实际的文档。这篇 MS KB文章指出，在不同归类之间，存在性能差异，但您想不到的地方。区别在于 SQL排序规则（向后兼容，但不支持Unicode）和 Windows排序规则（支持Unicode）：
<通常，Windows和SQL归类之间的性能差异不会很明显。仅当工作负载受CPU限制而不受到I / O或网络速度的约束时，这种差异才会出现，并且大部分CPU负担是由SQL Server中执行字符串操作或比较的开销引起的。
SQL和Windows归类均具有区分大小写和不区分大小写的版本，因此听起来这并不是主要问题。
Dan的出色文章来自战trench中另一个好故事，标题为 整理地狱：
我继承了混合整理环境整理的数量超出了我所能指望的范围。不同的归类要求变通办法以避免无法解决归类冲突错误，并且这些变通办法由于不可保留的表达式而导致性能下降。处理混合归类是一个真正的痛苦，因此，我强烈建议您对单个归类进行标准化，并且仅在经过深思熟虑后才偏离。
他总结道：
我个人认为在选择适当的排序规则时甚至不应该考虑性能。我生活在排序规则地狱中的原因之一是，我的前任选择了二进制排序规则，以充分发挥我们的高度事务性OLTP系统的所有性能。除了领先的通配符表扫描搜索之外，我发现我们的不同归类没有可衡量的性能差异。性能的真正关键是查询和索引调整，而不是归类。如果性能对您很重要，我建议您在基于性能期望选择排序规则之前，对实际的应用程序查询执行性能测试。
希望这会有所帮助。
What are the benefits/drawbacks of using a case insensitive collation in SQL Server (in terms of query performance)?
I have a database that is currently using a case-insensitive collation, and I don't really like it. I would very much like to change it to case sensitive. What should I be aware of when changing the collation?
(I added this as a separate answer because its substantially different than my first.) Ok, found some actual documentation. This MS KB article says that there are performance differences between different collations, but not where you think. The difference is between SQL collations (backward compatible, but not unicode aware) and Windows collations (unicode aware):
Generally, the degree of performance difference between the Windows and the SQL collations will not be significant. The difference only appears if a workload is CPU-bound, rather than being constrained by I/O or by network speed, and most of this CPU burden is caused by the overhead of string manipulation or comparisons performed in SQL Server.
Both SQL and Windows collations have case sensitive and case insensitive versions, so it sounds like that isn't the primary concern.
Another good story "from the trenches" in Dan's excellent article titled "Collation Hell":
I inherited a mixed collation environment with more collations than I can count on one hand. The different collations require workarounds to avoid "cannot resolve collation conflict" errors and those workarounds kill performance due to non-sargable expressions. Dealing with mixed collations is a real pain so I strongly recommend you standardize on a single collation and deviate only after careful forethought.
He concludes:
I personally don't think performance should even be considered in choosing the proper collation. One of the reasons I'm living in collation hell is that my predecessors chose binary collations to eke out every bit of performance for our highly transactional OLTP systems. With the sole exception of a leading wildcard table scan search, I've found no measurable performance difference with our different collations. The real key to performance is query and index tuning rather than collation. If performance is important to you, I recommend you perform a performance test with your actual application queries before you choose a collation on based on performance expectations.
Hope this helps.
这篇关于SQL Server不区分大小写的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
