sqlserver 排序规则（字符集）查看与修改

一、 定义

排序规则是根据特定语言和区域设置标准指定对字符串数据进行排序和比较的规则。

以 ORDER BY 子句为例：如果按升序排列，说英语的人认为字符串 Chiapas 应排在 Colima 之前。但是，对于在墨西哥说西班牙语的人来说，他们会认为以 Ch 开头的单词应显示在以 C 开头的单词列表的末尾。排序规则规定了这些排序和比较规则。Latin_1 General 排序规则在 ORDER BY子句中将 Chiapas 排在 Colima 之前，而 Traditional_Spanish 排序规则将 Chiapas 排在 Colima 之后。

 

二、 排序规则含义

排序规则指定了表示每个字符的位模式，还指定了用于排序和比较字符的规则。排序规则的特征是区分语言、区分大小写、区分重音、区分假名以及区分全半角。例如：

• 排序规则Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_UTF8表示：区分大小写，区分重音，区分假名，区分宽度和UTF-8编码。
• 排序规则Japanese_Bushu_Kakusu_140_CI_AI_KS_WS_VSS表示：不区分大小写，不区分重音，不区分假名，不区分宽度和变体选择符，并且使用非Unicode编码。

排序规则的主要含义如下：

• _BIN：指定使用向后兼容的二进制排序顺序。
• _BIN2：指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。
• _Stroke：按笔划排序
• _CI(CS) ：是否区分大小写，CI不区分，CS区分（case-insensitive/case-sensitive）
• _AI(AS)：是否区分重音，AI不区分，AS区分，排序时会将è、é视为不同的字符（accent-insensitive/accent-sensitive）
• _KI(KS) ：是否区分假名类型，KI不区分，KS区分（kanatype-insensitive/kanatype-sensitive）
• _WI(WS) ：是否区分全半角， WI不区分，WS区分（width-insensitive/width-sensitive）

完整版参考 https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15

 

三、 排序规则查看

1. 查看数据库支持哪些排序规则

select * from ::fn_helpcollations();

 

2. 查看实例排序规则

SELECT SERVERPROPERTY(N'Collation');

3. 查看数据库排序规则

SELECT name AS [DatabaseName], collation_name AS [Collation] FROM sys.databases;

4. 查看列排序规则

SELECT c.object_id, c.name, t.name, c.collation_name
FROM sys.columns c
LEFT JOIN sys.types t on t.system_type_id = c.system_type_id
WHERE  object_id=OBJECT_ID('Base_DealConcurrent');

 

四、 修改服务器排序规则

根据官方文档，修改服务器排序规则需要删除所有用户数据库，然后按指定排序规则重建master数据库（因此账号、作业、linkserver等都会丢失）。这是风险非常高的操作，如果业务已经在使用，务必慎重操作并做好备份。

 

1. 修改命令

修改方法其实很简单，只有一句命令，但执行前准备和执行后恢复可能很复杂。

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName

 

 

2. 推荐修改步骤

• 备份数据库，包括系统和用户数据库。
• 记录下修改过的服务器配置值。例如backup compression default、awe enabled等，修改服务器排序规则后，需要重新应用、配置这些值。以免遗漏，导致数据库性能等问题。
• 记录系统数据库的数据文件和日志文件的所在路径。 重新生成系统数据库会将所有系统数据库安装到其原位置。 如果你没有移动过系统数据库文件，这一步可以忽略。
• 整理账号、作业、linkserver信息。
• 如果有配置发布—订阅等，也需要整理相关的脚本、文档。
• 分离用户创建的数据库(这一步其实没有必要)。
• 修改服务器排序规则
• 附加Step 9分离的数据库。
• 将上面备份的信息重新在实例中进行配置

 

3. 修改常见报错

看似简单的操作过程，其实在不同的环境下，你总会遇到一些意外情况。

例1：sa密码强度不足

这个需要你修改sa的密码，满足强密码要求就可解决这个问题。

 

例2：不小心将/SAPWD中间多了几个空格，结果报如下错误

 

例3：附加数据库时，没有用sa账号，结果报如下错误，改用sa账号附加，问题解决

 

例4：另外以前也碰到过两个异常情况，一下子很难重现，以后遇到在补上。

 

另外执行上面脚本时，有可能时间比较长，也没啥提示，这时千万不要惊慌，耐心等待，如果你想了解进度，可以通过查看相关日志文件来查看进度，日志信息一般位于C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\ 下，例如C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130909_233902

 

五、 修改数据库排序规则

更改数据库排序规则时，需要更改下列内容：

• 数据库的默认排序规则，新的排序规则将应用于数据库中后续创建的所有列、用户定义的数据类型、变量和参数。根据数据库中定义的对象解析 SQL 语句中指定的对象标识符时，也使用新的默认排序规则。
• 将系统表中的任何 char、varchar、text、nchar、nvarchar 或 ntext 列更改为使用新的排序规则。
• 将存储过程和用户定义函数的所有现有 char、varchar、text、nchar、nvarchar 或 ntext 参数和标量返回值更改为使用新的排序规则。
• 将 char、varchar、text、nchar、nvarchar 或 ntext 系统数据类型和基于这些系统数据类型的所有用户定义的数据类型更改为使用新的默认排序规则。

 

修改方法1

ALTER DATABASE DataBaseName  COLLATE  Chinese_PRC_CI_AS

此时虽然修改了数据库的排序规则，但是先前用户创建的表的排序规则不会改变，仍然是旧的排序规则，你可以用下面SQL脚本验证。

SELECT c.object_id, c.name, t.name, c.collation_name
FROM sys.columns c
LEFT JOIN sys.types t on t.system_type_id = c.system_type_id
WHERE  object_id=OBJECT_ID('TableName');

这时可能还需要彻底修改这些对象的排序规则，那么可以看看这篇文章Easy way to change collation of all database objects in SQL Server,非常完美的介绍了如和操作。脚本都给你准备OK了。不过，建议做之前做好备份，有备无患。

 

如果有其它会话连接到数据库，修改数据库排序规则时会报5030错误，如下所示：

ALTER DATABASE MESDB COLLATE  Chinese_PRC_CI_AS
消息 5030，级别 16，状态 2，第 1 行
The database could not be exclusively locked to perform the operation.
消息 5072，级别 16，状态 1，第 1 行
ALTER DATABASE failed. The default collation of database 'MESDB' cannot be set to Chinese_PRC_CI_AS.
Msg 468, Level 16, State 9, Procedure FN_GET_GO_PRE_CUT_DETAIL, Line 69
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_PRC_90_CI_AS" in the equal to operation.

此时你必须断开其它会话，通常用下面步骤实现：

--修改为单用户模式
ALTER DATABASE  DataBaseName   SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--修改数据库排序规则
ALTER DATABASE MESDB COLLATE  Chinese_PRC_CI_AS
--修改为多用户模式
ALTER DATABASE DataBaseName SET MULTI_USER;

 

修改方法2：

导出创建数据库各类对象的脚本，然后替换相应的排序规则，这种方法适合丢弃数据，只保留结构的方式，如果还要考虑数据，那么是个复杂、繁琐的实现方法。

 

六、 修改列排序规则

ALTER TABLE TEST2 ALTER COLUMN NAME CHAR(120)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;

 

七、 排序规则冲突

1. SQL 脚本使用临时表时，出现下面错误（服务器排序规则与数据库排序规则不一致）

Cannot resolve the collation conflict between "Chinese_PRC_90_CI_AS" and "Chinese_PRC_CI_AS" in the equal to operation.

一般出现这种情况，通常通过显示指定临时表相关列的排序规则或在SQL 脚本里面显示指定排序规则解决。其本质是因为tempdb与用户数据库的排序规则不一致导致的。

 

2. 两台服务器上的数据库通过链接服务器交换操作时，由于排序规则，有可能导致查询语句超级慢，出现这个情况，是由于转换时，导致查询计划不走索引，走全表扫描所致。

 

参考资料：

http://msdn.microsoft.com/zh-cn/library/ms179254(v=sql.90).aspx

http://blog.csdn.net/feixianxxx/article/details/4633610

http://msdn.microsoft.com/zh-cn/library/ms143508(v=sql.100).aspx

http://msdn.microsoft.com/zh-cn/library/cc281995(v=SQL.100).aspx

http://www.cnblogs.com/JamesLi2015/archive/2013/05/07/3065022.html

http://msdn.microsoft.com/zh-cn/library/cc835499.aspx

http://www.cnblogs.com/blodfox777/archive/2010/01/21/sqlserver-collation-conflict-and-solutions.html

http://www.cnblogs.com/kerrycode/p/3365956.html

http://www.mssqlmct.cn/dba/?post=13