So I have two SQL Servers at the moment, both 2012 and both running separate systems.
所以我现在有两个SQL服务器，都是2012年的，而且都运行独立的系统。
One of these servers runs on a collation of latin1_general_bin and the other runs with latin1_general_ci_as, ideally I'd like to shut one of these servers off and move all the systems to one place.
其中一个服务器运行在latin1_general_bin的排序上，另一个服务器运行在latin1_general_ci_as上，理想情况下，我希望关闭其中一个服务器并将所有系统转移到一个位置。
I know this can be done technically, however I was wondering if this was a good idea? Will I be causing myself more problems down the line?
我知道这在技术上是可以做到的，但是我想知道这是不是一个好主意?我会给自己造成更多的麻烦吗?
Both databases are transactional based and are defined by external parties so cannot have their collation changed. If I were as to do this, would it be better to have the server collation set as latin1_general_bin or latin1_general_ci_as? I'm thinking that the case sensitive option would be better as queries would still run against case insensitive databases whilst case insensitive queries would need to be carefully managed
这两个数据库都是基于事务的，并且由外部方定义，因此不能更改它们的排序规则。如果我这样做，服务器排序集设置为latin1_general_bin或latin1_general_ci_as会更好吗?我认为区分大小写的选项会更好，因为查询仍然运行在不区分大小写的数据库上，而区分大小写的查询需要仔细管理
2 个解决方案
#1
4
There should be no problem using databases with different collations on one server. One of our external vendors also provides a database with different collation and this has been working without issues since SQL Server 2000. However, you will have to explicitly specify the collation (or use collation database_default when you want to join tables from the different databases. From my own experience, that can have quite a performance impact. You also need to be careful when using SELECT INTO when creating a new table as this can result in mixed collation in a database or table.
在一个服务器上使用不同排序的数据库是没有问题的。我们的一个外部供应商还提供了一个具有不同排序的数据库，自SQL Server 2000以来，这个数据库一直运行正常。但是，您必须显式地指定排序规则(或者当您希望连接来自不同数据库的表时，使用排序规则database_default。根据我自己的经验，这可能会对性能产生相当大的影响。在创建新表时使用SELECT INTO时也需要小心，因为这会导致数据库或表中的混合排序。
#2
2
Yes, SQL Server keeps the collation neatly separated with the databases. But there is a big flaw, which caused severe problems with one of our customers:
是的，SQL Server将排序规则与数据库整齐地分开。但有一个很大的缺陷，给我们的一个客户带来了严重的问题:
The temp table (CREATE TABLE #Tbl ...) uses the server's default collation if you do not specify the collation explicitly.
如果不显式指定排序规则，temp表(创建表#Tbl…)将使用服务器的默认排序规则。
In one of our projects, especially with quite old stored Procedures created with SQL Server 2000, there was heavy usage of temp tables to store intermediate results.
在我们的一个项目中，特别是使用SQL Server 2000创建的非常旧的存储过程时，大量使用临时表来存储中间结果。
The customer ran into crazy errors and it took a hell of a lot of time to track this down. He did not want to change his server (due to other databases running there). So we changed the database to his collation. After this some of our upgrade scripts did not run anymore... It ended with all databases changed to the same collation and the installation of another instance of SQL Server.
客户遇到了疯狂的错误，花了大量的时间来跟踪它。他不想改变他的服务器(因为其他数据库在那里运行)。所以我们把数据库改成了他的排序。在这之后，我们的一些升级脚本不再运行了…最后，所有数据库都被更改为相同的排序，并安装了另一个SQL Server实例。
Which collation is the best for you? I don't know... This depends on your needs. Are you dealing with diacritic characters a lot (look at "_as"), do you want "test" to be equal with "TeSt" (look at "_ci")
哪个校勘最适合你?我不知道…这取决于你的需要。您是否处理了很多字符(查看“_as”)，您是否希望“测试”与“测试”相等(看“_ci”)
#1
4
There should be no problem using databases with different collations on one server. One of our external vendors also provides a database with different collation and this has been working without issues since SQL Server 2000. However, you will have to explicitly specify the collation (or use collation database_default when you want to join tables from the different databases. From my own experience, that can have quite a performance impact. You also need to be careful when using SELECT INTO when creating a new table as this can result in mixed collation in a database or table.
在一个服务器上使用不同排序的数据库是没有问题的。我们的一个外部供应商还提供了一个具有不同排序的数据库，自SQL Server 2000以来，这个数据库一直运行正常。但是，您必须显式地指定排序规则(或者当您希望连接来自不同数据库的表时，使用排序规则database_default。根据我自己的经验，这可能会对性能产生相当大的影响。在创建新表时使用SELECT INTO时也需要小心，因为这会导致数据库或表中的混合排序。
#2
2
Yes, SQL Server keeps the collation neatly separated with the databases. But there is a big flaw, which caused severe problems with one of our customers:
是的，SQL Server将排序规则与数据库整齐地分开。但有一个很大的缺陷，给我们的一个客户带来了严重的问题:
The temp table (CREATE TABLE #Tbl ...) uses the server's default collation if you do not specify the collation explicitly.
如果不显式指定排序规则，temp表(创建表#Tbl…)将使用服务器的默认排序规则。
In one of our projects, especially with quite old stored Procedures created with SQL Server 2000, there was heavy usage of temp tables to store intermediate results.
在我们的一个项目中，特别是使用SQL Server 2000创建的非常旧的存储过程时，大量使用临时表来存储中间结果。
The customer ran into crazy errors and it took a hell of a lot of time to track this down. He did not want to change his server (due to other databases running there). So we changed the database to his collation. After this some of our upgrade scripts did not run anymore... It ended with all databases changed to the same collation and the installation of another instance of SQL Server.
客户遇到了疯狂的错误，花了大量的时间来跟踪它。他不想改变他的服务器(因为其他数据库在那里运行)。所以我们把数据库改成了他的排序。在这之后，我们的一些升级脚本不再运行了…最后，所有数据库都被更改为相同的排序，并安装了另一个SQL Server实例。
Which collation is the best for you? I don't know... This depends on your needs. Are you dealing with diacritic characters a lot (look at "_as"), do you want "test" to be equal with "TeSt" (look at "_ci")
哪个校勘最适合你?我不知道…这取决于你的需要。您是否处理了很多字符(查看“_as”)，您是否希望“测试”与“测试”相等(看“_ci”)