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.
我们正在将数据库从一个旧的SQL Server 2k EE服务器上迁移到新的SQL Server 2005和2008服务器上，该服务器具有默认的排序“SQL_Latin1_General_CP1_CI_AS”。据我所知，没有需要Unicode的国际字符，所以这两个代码页在实际应用中几乎是相同的。
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.
SQL Server的主DBA坚持认为，每个数据库(大部分由第三方应用程序构建)在迁移之前都必须使用新的排序规则进行重新构建。
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?).
我知道，自从SQL Server 2000以来，就可以将单个数据库设置为与默认数据库不同的排序规则。但混合排序的真正后果是什么呢?例如，微软的一篇文章提出了共享tempdb的复杂之处(但是可以很容易地避免吗?)
And, perhaps more importantly, what might we do to avoid these problems if we do need to support multiple collations on the new servers?
而且，或许更重要的是，如果我们确实需要支持新服务器上的多个排序，我们该如何避免这些问题呢?
3 个解决方案
#1
4
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.
我真的希望你好运，这可能是一个普遍和棘手的问题，我不会希望任何人。
#2
11
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.
服务器和db之间不同排序的问题正如前面提到的那样，临时表将默认使用服务器排序创建。这会导致临时表和常规表之间的字符字段比较失败。通过对temp表的字符字段使用COLLATE database_default，第三方应用程序的开发人员可以避免这种情况。
create table #Tmp(Col1 nvarchar(50) COLLATE database_default)
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.
我来自“另一边”。我不是DBA，而是第三方软件开发人员，我认为我有责任在数据库和服务器之间排序不同的环境中构建我的应用程序。我的应用程序可以进行区分大小写的排序，这也是我的责任。
#3
2
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
啊:而且在T-SQl指令中也有大写\小写的问题……检查这个
@Michael and your DBA are right .... limit the risks, and use a unique collation.
@Michael和DBA是正确的....限制风险，使用独特的排序。
#1
4
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.
我真的希望你好运，这可能是一个普遍和棘手的问题，我不会希望任何人。
#2
11
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.
服务器和db之间不同排序的问题正如前面提到的那样，临时表将默认使用服务器排序创建。这会导致临时表和常规表之间的字符字段比较失败。通过对temp表的字符字段使用COLLATE database_default，第三方应用程序的开发人员可以避免这种情况。
create table #Tmp(Col1 nvarchar(50) COLLATE database_default)
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.
我来自“另一边”。我不是DBA，而是第三方软件开发人员，我认为我有责任在数据库和服务器之间排序不同的环境中构建我的应用程序。我的应用程序可以进行区分大小写的排序，这也是我的责任。
#3
2
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
啊:而且在T-SQl指令中也有大写\小写的问题……检查这个
@Michael and your DBA are right .... limit the risks, and use a unique collation.
@Michael和DBA是正确的....限制风险，使用独特的排序。