MS SQL 排序规则总结

排序规则术语

 

     什么是排序规则呢？ 排序规则是根据特定语言和区域设置标准指定对字符串数据进行排序和比较的规则。SQL Server 支持在单个数据库中存储具有不同排序规则的对象。MSDN解释：在 Microsoft SQL Server中，字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则

当 Transact-SQL 语句在具有不同排序规则设置的不同数据库上下文中运行时，其运行结果可能会不同。如果可能，请为您的组织使用标准化排序规则。我管理的数据库当中，一般情况下，中国区域设置为Chinese_PRC_CI_AS,国外一般设置SQL_Latin1_General_CP1_CI_AS。这样就不必显式指定每个字符或 Unicode 表达式中的排序规则。如果必须使用具有不同排序规则和代码页设置的对象，请对查询进行编码，以考虑排序规则的优先顺序规则。

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

Chinese_PRC_CI_AS   前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则，CI表示不区分大小写，AS表示区分重音。

排序规则的后半部份即后缀 含义：

_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)

区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。

区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，比较还将重音不同的字母视为不等。
区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项。

查看数据库支持哪些排序规则可以通过下面系统函数查看：

select * from ::fn_helpcollations();

 

排序规则类型

      

SQL Server 提供了两组排序规则：Windows 排序规则和 SQL Server 排序规则。具体参考MSDN，这里不做过多赘述。

 

查看服务器排序规则(数据库实例排序规则)

SELECT SERVERPROPERTY(N'Collation')

 

查看数据库排序规则

SQL 1:

SELECT DATABASEPROPERTYEX('DBMonitor', 'Collation')

SQL 2:

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

查看列排序规则

SQL 1:

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('Test');

修改服务器排序规则

 

      修改服务器的排序规则的原因千差万别，大部分情况是由于安装的时候，忽略了服务器排序规则这个选项设定，没有事前规划好，等到将数据库还原或迁移到新服务器上，测试过程中才发现问题。

修改服务器排序规则，其实就是按指定排序规则重新生成 master、model、msdb 和 tempdb 等系统数据库，修改服务器排序规则中，首先删除这些系统数据库，然后在默认位置按指定排序规则重新创建。 修改数据库实例排序规则时，由于删除、重建系统数据库。 用户对这些数据库所做的所有修改都会丢失。 例如，账号信息、作业、链接服务器等等。所以修改服务器排序规则要慎重，首先做好备份或整理相关账号、作业、链接服务器、邮件配置信息等。以免仓促造成不必要的麻烦或损失。

MSDN关于设置和更改服务器排序规则

SQL 2005

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

SQL 2008

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

服务器排序规则修改起来看似很简单，其实不然，一不小心，就会让你万劫不复。我在实践中就碰到过一次疏忽了某个步骤，结果让我惊出一身冷汗。下面是我自己实施的步骤：

Step 1： 首先备份数据库（包括系统数据库和用户数据库）。记得千万不要漏掉了系统数据库备份。有备才能无患，否则每一步操作，你总要提心吊胆。

Step 2： 在文档上记录下你修改过的一些服务器配置值。例如，在SQL SERVER 2008中，你有可能启用backup compression default ；在某个32位数据库开启了awe enabled 选项，那么修改服务器排序规则后，你需要重新应用、配置这些值。以免遗漏，导致数据库性能等问题。

Step 3：记录一下系统数据库的数据文件和日志文件的所在路径。 重新生成系统数据库会将所有系统数据库安装到其原位置。 如果你没有移动过系统数据库数据库文件或日志文件，这一步可以忽略，像很多时候，为了I/O性能等原因，可能移动过这些系统数据库文件和日志文件。

Step 4： 用文档将登录名（logins）和相关密码整理出来。因为修改服务器排序规则，实则重建系统数据库master、msdb、tempd等，登录名等信息会全部没有，需要重新创建、配置。

Step 5： 生成已有作业的SQL脚本。方便修改服务器排序规则后，重新创建、部署作业。道理同上。

Step 6： 生成已有链接服务器的排序规则，方便修改服务器排序规则后，重新创建、部署链接服务器。道理同上。

Step 7： 整理一下数据库邮件配置文件和已经创建的账号，方便修改服务器排序规则后，重新配置。

Step 8： 如果在实例上有配置发布—订阅等，那么也需要整理这些相关的脚本、文档。

Step 9： 分离用户创建的数据库(这一步其实没有必要)。

Step 10：修改服务器排序规则

Step 11: 附加Step 9分离的数据库。

Step 12：解决孤立账号、配置作业、链接服务器…..

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

例1：

D:软件工具SQL SERVER 2008>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=M

SSQLSERVER /SQLSYSADMINACCOUNTS=sa /SAPWD=123456 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Microsoft (R) SQL Server 2008

The following error occurred:

指定的 sa 密码不满足强密码要求。有关强密码要求的更多信息，请参见安装程序帮助或 SQL Server 2008 联机丛书中的“数据库引擎配置 - 帐户设置”。

Error result: -2068578304

Result facility code: 1204

Result error code: 0

Please review the summary.txt log for further details

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

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

 

例3：附加数据库时，没有用sa账号，而是用sa创建的windows 身份登录验证账号附加数据库，结果报如下错误，改用sa账号附加，问题解决

 

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

 

另外执行上面脚本时，有可能时间比较长，此时你有没啥提示，这时千万不要惊慌，耐心等待，如果你想了解进度，可以通过查看相关日志文件来查看进度，日志信息一般位于C:Program FilesMicrosoft SQL Server100Setup BootstrapLog 下，例如C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20130909_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