解决排序规则(collation)冲突的办法(SQL Server 问题之 排序规则(collation)冲突)

第一篇参考

一、写在前面

　　最近公司进行开发环境升级，数据库也准备了一个新的服务器，一切准备好后开始数据迁移，采取的方式为对现有Database（现有服务器Windows Server 2003 + SQL Server 2005）进行Back up，然后在新服务器（Windows Server 2008 R2 + SQL Server 2008 R2）上再Restore Databse，一切似乎挺顺利的。但是当从Web Server开始访问的时候出现错误Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

 

二、问题分析

　　上面出现的问题是由于排序规则冲突（collation）而引发，具体说来就是数据库所使用的语言的排序规则冲突，查看数据库所（老服务器）使用的排序规则如下所示

　　而新服务器上数据库的排序规则为SQL_Latin1_General_CP1_CI_AS（就不上图了），由此可以看出排序规则不同，此即问题所在。

　　那么为什么数据库的排序规则会不同呢？换句话说数据库的排序规则是怎么来的？原来这里的排序规则是在安装SQL Server数据库实例时配置/指定的，默认的排序规则将基于操作系统的区域语言设置，我们新服务器OS的区域语言为US - English，而在安装SQL Server数据库实例时我们又没有配置排序规则（采取了默认的方式），所以才导致最终数据库的排序规则为SQL_Latin1_General_CP1_CI_AS

 

三、解决问题

　　下面列出解决此问题可能的方式以及说明

1. 在SQL语句中强行指定排序规则，类似的SQL语句如下所示 (COLLATE Chinese_PRC_CI_AI_WS)

SELECT A.ID, B.NO 
FROM TABLEA A INNER JOIN TABLEB B 
ON A.NAME=B.NAME COLLATE Chinese_PRC_CI_AI_WS

这种方式虽能解决问题，但是不建议采取，因为一旦需要更改的SQL语句很多，对开发者而言将是一种灾难，而且不能从根本上解决问题

2. 更改字段、表、数据库的排序规则

脚本更改数据库的排序规则

ALTER DATABASE DBNAME COLLATE Chinese_PRC_CI_AS

虽然通过更改数据库级别的排序规则能很大程度上解决大部分问题，但是一旦用到TempDB（确切地说使用到临时表，例如Store Procedure中），排序规则问题将再次面临，而且致命的是系统数据库master貌似无法更改排序规则，至少笔者通过脚本运行的时候提示没有权限运行，所以此种方式也没有从根本上解决问题。

说明：重建数据库也是不行的，只要master的排序规则没变你就无法解决问题。

3. 承接上面的第二点，既然无法通过脚本更改master数据库的排序规则，那么就重建master数据库

执行类似下面的脚本

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Chinese_PRC_CI_AS

或参考MSDN的文章http://support.microsoft.com/kb/298568/zh-cn

笔者没有验证此种方式是否有效（笔者采用上面的脚本运行，但是没成功）

4. 卸载/重装SQL Server实例

首先更改操作系统的区域语言为CN - Chinese，然后卸载并重装SQL Server，之所以更改系统的区域语言设置，是为了在安装SQL Server的时候可以采用默认的排序规则（当然你也可以不更改区域语言设置，但在安装SQL Server的时候手工配置/指定排序规则为Chinese_PRC_CI_AS，到“排序规则设置”界面时，系统默认选择的是以下拉框的形式选择排序规则的选项），此种方法彻底解决问题，推荐使用。

源自 http://www.cnblogs.com/panchunting/p/SQLServer_Issue_Collation.html

第二篇参考

修改SQL Server 的排序规则

2012-12-21 09:46:32

源自http://jimshu.blog.51cto.com/3171847/1095780

一、修改SQL Server服务器（实例）的排序规则

　　以下实验使用了SQL Server 2008 R2的默认实例，将Chinese_PRC_CI_AS修改成SQL_Latin1_General_CP1_CI_AS。

 

1. 停止SQL Server实例服务

2. 打开“命令提示符”，转到SQL Server的安装目录

C:\Users\Administrator> cd "\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2"

 

3. 运行setup

C:\Program FIles\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2> setup /QUIET /ACTION=REBUILDDATABASE /instancename=mssqlserver /SQLSYSADMINACCOUNTS=administrator /sqlcollation=SQL_Latin1_General_CP1_CI_AS

 

4. 启动SQL Server实例服务

 

5. 验证实例的排序规则

 

6. 验证系统数据库（master、model、msdb、tempdb）的排序规则

 

说明：此操作仅影响系统数据库。对于已经存在的用户数据库无效。

 

二、修改数据库的排序规则

　　以下实验将修改数据库db01的排序规则，将SQL_Latin1_General_CP1_CI_AS修改成Chinese_PRC_CI_AS。

 

1. SSMS图形界面

 

 

2. 脚本

ALTER DATABASE [db01] COLLATE Chinese_PRC_CI_AS

 

3. 局限性

（1）对于已经存在的数据，此操作并不会导致立即重新排序。

（2）如果已经有对象依赖于数据库排序规则，则更改不成功。

 

 

三、修改表的排序规则

　　表的排序规则依赖于数据库的排序规则。不能修改。

 

 

四、修改列的排序规则

　　建议：将这个表的数据保存到另一个临时表，重建这个表，然后从临时表将数据导入到新表。

　　可以通过SSMS修改某一列的排序规则，但是，这项操作实际上还是删除这个表以及相关的约束、触发器，然后重建这个表、约束、索引、触发器。

 

 

ALTER TABLE dbo.EmpBasic DROP CONSTRAINT DF__EmpBasic__FirstN__2319CD4B
ALTER TABLE dbo.EmpBasic DROP CONSTRAINT ......

CREATE TABLE dbo.Tmp_EmpBasic ( ......
FirstName nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
...... ) ON [PRIMARY]

ALTER TABLE dbo.Tmp_EmpBasic SET (LOCK_ESCALATION = TABLE)
GRANT DELETE ON dbo.Tmp_EmpBasic TO public AS dbo
GRANT INSERT ON dbo.Tmp_EmpBasic TO public AS dbo
GRANT SELECT ON dbo.Tmp_EmpBasic TO public AS dbo
GRANT UPDATE ON dbo.Tmp_EmpBasic TO public AS dbo

ALTER TABLE dbo.Tmp_EmpBasic ADD CONSTRAINT DF__EmpBasic__FirstN__2319CD4B DEFAULT ('') FOR FirstName
ALTER TABLE dbo.Tmp_EmpBasic ADD CONSTRAINT DF__EmpBasic__Middle__240DF184 ......

SET IDENTITY_INSERT dbo.Tmp_EmpBasic ON

IF EXISTS(SELECT * FROM dbo.EmpBasic)
EXEC('INSERT INTO dbo.Tmp_EmpBasic (Company, EmpID, FirstName,......)
SELECT Company, EmpID, FirstName, ...... FROM dbo.EmpBasic WITH (HOLDLOCK

TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_EmpBasic OFF

DROP TABLE dbo.EmpBasic
EXECUTE sp_rename N'dbo.Tmp_EmpBasic', N'EmpBasic', 'OBJECT'

CREATE UNIQUE CLUSTERED INDEX EmpID ON dbo.EmpBasic
(Company, EmpID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX FirstName ON dbo.EmpBasic
( Company, FirstName, LastName ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

create trigger _ti_EmpBasic ON dbo.EmpBasic for insert as
begin
......
end