SQL Server 数据库的排序规则与服务器默认排序规则不同是否会带来麻烦？
我们正在将数据库从默认排序规则“Latin1_General_CI_AS”的旧 SQL Server 2k EE 服务器迁移到新的 SQL Server 2005 和 SQL Server 2005。 2008 服务器具有默认排序规则“SQL_Latin1_General_CP1_CI_AS”。据我所知，没有需要 Unicode 的国际字符，因此这两个代码页在实际应用中几乎是相同的。
主要的 SQL Server DBA 坚持认为，每个数据库（其中大部分是由第 3 方应用程序构建的）都必须使用新的排序规则进行重建，然后才能迁移它们。
我知道自 SQL Server 2000 以来，就可以将各个数据库设置为具有与默认值不同的排序规则。 但是使用混合排序规则运行的真正后果是什么？
而且，也许更重要的是，如果我们确实需要在新服务器上支持多种排序规则，我们可以采取什么措施来避免这些问题？
We are in the process of migrating databases off an old SQL Server 2k EE server with default collation "Latin1_General_CI_AS" onto new SQL Server 2005 & 2008 servers with default collation "SQL_Latin1_General_CP1_CI_AS". There are no international characters that would require Unicode that I know of, so the two codepages are almost the same for practical purposes.
The primary SQL Server DBA is adamant that every single database (most of which are built by 3rd-party apps) must be rebuilt with the new collation before he will migrate them.
I know that ever since SQL Server 2000 it's been possible to set individual databases to have a different collation than the default. But what are the real consequences of running with mixed collations? One article from Microsoft suggests complications with the shared tempdb, for example (but can it easily be avoided?).
And, perhaps more importantly, what might we do to avoid these problems if we do need to support multiple collations on the new servers?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
服务器和数据库之间不同排序规则的问题正如之前提到的，临时表将默认使用服务器排序规则创建。这将使临时表和常规表之间的字符字段的任何比较失败。第三方应用程序的开发人员可以通过对字符字段使用 COLLATE database_default 来避免这种情况临时表。
我来自“另一”方。我不是 DBA，而是第 3 方软件开发人员，我认为我有责任构建我的应用程序，以便在数据库和服务器之间的排序规则不同的环境中工作。我的应用程序将使用区分大小写的排序规则也是我的责任。
The problem with different collations between server and db is as is mention before that temp tables will default be created with the server collation. That will make any comparisons on character fields between a temp table and a regular table fail. This can be avoided by the developers of the 3rd-party apps by using COLLATE database_default for character fields of temp tables.
I come from the "other" side. I'm not a DBA but a 3rd party software developer and I think that it is my responsibility to build my app to work in an environment where the collation is different between database and server. It is also my responsibility that my app will work with case sensitive collation.
好吧，这不是最好的答案，但
您问：“使用不同排序规则运行的真正后果是什么”
这可能会令人头痛。你提到的微软的文章一针见血。根据我的个人经验，我遇到过这个问题，而且不容易避免。
除非你测试得好，否则不匹配的排序规则会出现在计划外的地方。
您还问“如果我们确实需要在新服务器上支持多种排序规则，我们可以采取什么措施来避免这些问题？”
除了疯狂地测试之外，我脑子里什么也没有想到。
我真的祝你好运，这可能是一个常见且棘手的问题，我不希望任何人遇到这种问题。
Okay not the best answer, but
You asked: "What are the real consequences of running with different collations"
It can be a headache. The article you mentioned by Microsoft nails it on the head. In my personal experience I've come across that issue and it wasn't easy to avoid.
Mismatched collations will pop up in unplanned places unless you test well.
You also asked "what might we do to avoid these problems if we do need to support multiple collations on the new servers?"
Nothing comes to mind except to test like crazy.
I really wish you luck, it can be a common and hairy problem that I wouldn't wish on anyone.
我的答案也不是一个好答案，但是：
我们有多个订阅者服务器与我们的主数据库同步，并且其中一些服务器上有一个不是发布者的排序规则。启动复制时，我们不断收到此“欢迎消息”，告诉我们“由于排序规则不相同，同步可能不会成功”。
尽管这个问题从未发生过，但我猜想某个地方存在风险，并且我认为这种风险可能与引用完整性和/或在字符字段上设置的其他约束之类的事情有关。
啊：T-SQl 指令中也存在大写\小写问题...检查这个 此处
@Michael 和您的 DBA 是对的......限制风险，并使用独特的排序规则。
My answer is not a good one too, but:
we have multiple subscriber servers synchronizing with our main database, and on some of them have a collation which is not the publisher's one. When launching replication, we keep on getting this "welcome message" telling us that, "as collations are not identical, the synch might not succeed".
Though this problem never occured, I guess there is a risk somewhere, and I think this risk could be linked to things like referential integrity and\or other constraints set on character fields.
Ah: and there is also this uppercase\lowercase issue in T-SQl instructions ... check this one here
@Michael and your DBA are right .... limit the risks, and use a unique collation.