[SQL] SQLServer修改DB的collation, 解决排序字规则字符集问题

以前用别人备份的sqlserver的srcipt在执行创建视图操作的时候，出现了如下错误, 昨天在做另一个system的batch job的时候执行一个关联语句又出现了这个错误, 第二天总结的时候发现, 要想更改DB的排序排序集的时候,一定要依次将库 --表 – 列 三者的Collation字符排序集都改一遍才可以。

Cannot resolve the collation conflict between “Japanese_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

查看修改库的Collation排序字符集：因为是原来本地DB中没有这个System的shcema, 是另外一个同事给我的这个schema的备份文件Axx.bk文件,在通过Restore Database还原DB后执行跨库关联语句的时候出现问题, 在遇到这个问题后的第一个意识就是去修改该还原DB的Collation 使其在跨库之前的表的Collation保持一致

在右键查看Schema的属性中发现数据库的Collation是灰色无法变更的, 在这里我想到了是否有SQL语句直接来修改Collation：

1. ALTER DATABASE db_databaseSET SINGLE_USER WITH ROLLBACK IMMEDIATE–修改为单用户模式
2. ALTER DATABASE db_databaseCOLLATE Japanese_CI_AS–关闭所有的查询窗口, 修改数据库排序集
3. ALTER DATABASE db_databaseSET MULTI_USER --再修改为多用户模式

查看修改表的Collation排序集：依次执行完上面三个语句后再次查看DB属性中的Collation发现已经变更为预想设置的排序集, 而在修改完排序集Collation后,再次执行跨库关联语句依然再次出现此问题, 查看表的排序字符集 右键表名 Properties(属性) – Extended(扩展属性) 中查看Collation. 因为表的Collation排序集依赖于库的排序字符集, 所以表的字符集已经变更Japanese_CI_AS

查看修改列的Collation排序集：在右键查看Schema的属性中发现数据库的Collation是灰色无法变更的, 在这里我想到了是否有SQL语句直接来修改Collation：

点击右侧…按钮出现修改Collation窗口如下图, 点击Restore Default按钮, 会自动设置成本地初始的DB排序字符集

SQL语句修改列的Collation ：

ALTER COLUMN colnamenvarchar(100) COLLATE Japanese_CI_AS

注意：在这里你会意识到一个问题, 只是单独修改一列, 如果要修改几十列上百列岂不是累死了.当时自己处理的方法是将该Table备份为临时表之后再创建新的table导入原始数据, 另外在利用SSMS的修改列的Collation的时候实际上是删除这个表以及相关的约束、触发器，然后重建这个表、约束、索引、触发器.

先利用Create脚本创建出来临时表之后按照下列语句导入数据, 注意不要直接使用SELECT * INTO TempTable FROM Ttable, 这样创备份出来的列的Collation依然是不对的。

SET IDENTITY_INSERT TempTableON
INSERT INTO TempProductRule (ID, ProductID) SELECT ID, ProductID FROM Ttable

到此为止基本关于Collation的问题便得到解决, 如果是别人备份的脚本文件中带有Collation在不同排序集的DB中运行的时候出现错误, 也可以在相关SQL语句中直接指定Collation, 这样便不需要修改DB相关的Collation了,方法请查看：解决办法： Cannot resolve the collation conflict between “Japanese_CI_AS” and "SQL

另外一种方法是修改SQL Server服务器（SQL Server实例）的Collation字符集排序,但是这种修改只能修改System系统数据库, 对于已经存在的用户数据库是不起作用的. 方法如下：

1. 在命令行cmd面板中 通过CD PATH 命令 进入SQL Server 安装文件所在目录（setup.exe文件所在目录）
2. 运行命令：Net stop mssqlserver
3. Setup /QUIET /ACTION=REBUILDDATABASE /instancename=mssqlserver /SQLSYSADMINACCOUNTS=administrator pwd=XXXXXX lcollation=Japanese_CI_AS
4. Net start mssqlserver