SQL Server 2008 R2 排序规则详解

  一、 使用排序规则

排序规则指定字符串数据如何比较和排序的规则，基于特定的语言与区域标准。例如，在ORDER BY子句中，如果按升序排列的话，说英语的人会期望字符串'Chiapas'出现在'Colima'之前；然而，说西班牙语的墨西哥人将期望以‘Ch’开头的单词出现在以'C'开头的单词列表的末尾。排序规则负责控制这些类型的比较与排序规则。 在 ORDER BY ASC的子句中，Latin_1 General 排序规则将‘Chiapas’排在‘Colima’之前，而Traditional_Spanish 排序规则将‘Chiapas’排在‘Colima’之后。

当为非Unicode字符数据如char,varchar,text指定排序规则时，一个特定的code page将与之关联。例如，如果数据表中为char类型的某列定义了Latin1_General 排序规则，那么，SQL Server将使用1252 code page解释和显示该列中的数据。

对于非Unicode数据，多个排序规则可以使用相同的code page。而对于纯Unicode数据如nchar,nvarchar,nvachar(max),指定的排序规则则没有与之关联的code page。Unicode数据能够处理大多数的通用字符。

二、 代码页体系结构

排序规则控制SQL Server中字符串的物理存储。它指定了表示每个字符的bit排列方式，以及字符比较和排序的规则。
在计算机中，字符表示为ON/OFF的不同bit排列方式。一个字节有8个bits，8个bits就有256种不同的ON/OFF排列。通过为每个字符分配一种bit排列方式，每个字符占用1个字节存储的程序因此最多可以表示256个不同的字符。2个字节有16个bits，16个bits就有65536中不同的ON/OFF排列方式。使用2个字节表示1个字符的程序最多能够表示65536个不同字符。
单字节code pages就是将字符映射到一个字节中可能的256种bit排列方式中的每一种的定义。Code page为大写字符、小写字符、数字、符号以及特殊字符如！,@,#,%等定义bit排列方式。每一种欧洲语言如德语或西班牙语都有自己的单字节Code page。尽管用于表示从A到Z的拉丁字母表字符的bit排列方式都是相同的，但是，表示重音符号字符的bit排列方式则随code page而不同。
对于许多语言来说，单字节字符集不能存储所有字符。有些亚洲语言有成千上万个字符。因此，它们必须使用2个字节表示一个字符。已经为许多语言定义了双字节字符集，以及与之相关的code page。

三、 排序规则分类

SQL Server提供了2组排序规则：Windows排序规则和SQL Server排序规则。

Windows排序规则命名

Syntax
<Windows_collation_name> :: = 
     CollationDesignator_<ComparisonStyle>
<ComparisonStyle> :: = 
    { CaseSensitivity_AccentSensitivity
        [ _KanatypeSensitive ] [ _WidthSensitive ]  }
  | { _BIN | _BIN2 }

Arguments
CollationDesignator

Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:

• The sorting rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
• The code page used to store non-Unicode character data.

Some examples are:

• Latin1_General or French: both use code page 1252.
• Turkish: uses code page 1254.

CaseSensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

AccentSensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

KanatypeSensitive

Omitted specifies kanatype-insensitive, KS specifies kanatype-sensitive.

WidthSensitivity

Omitted specifies width-insensitive, WS specifies width-sensitive.

BIN

Specifies the backward-compatible binary sort order to be used.

BIN2

Specifies the binary sort order that uses code-point comparison semantics introduced in SQL Server 2005.

Notes：
Kanatype Sensitive

Distinguishes between the two types of Japanese kana characters: Hiragana and Katakana.
If this option is not selected, SQL Server considers Hiragana and Katakana characters to be equal for sorting purposes

Width Sensitive

Distinguishes between a single-byte character and the same character when represented as a double-byte character.
If this option is not selected, SQL Server considers the single-byte and double-byte representation of the same character to be identical for sorting purposes.

SQL Server排序规则命名

Syntax 
<SQL_collation_name> :: = 
     SQL_SortRules[_Pref]_CPCodepage_<ComparisonStyle>
<ComparisonStyle> ::=
    _CaseSensitivity_AccentSensitivity | _BIN

Arguments
SortRules

A string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified. Examples are Latin1_General or Polish.

Pref

Specifies uppercase preference.

Codepage

Specifies a one- to four-digit number that identifies the code page used by the collation. CP1 specifies code page 1252, for all other code pages the complete code page number is specified. For example, CP1251 specifies code page 1251 and CP850 specifies code page 850.

CaseSensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

AccentSensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

BIN

Specifies the binary sort order to be used.

四、 选择排序规则

如果你的SQL Server实例的所有用户都说同样的语言，你应该选择支持该语言的排序规则。比如，如果所有用户都说法语，就选择French排序规则。如果你的SQL Server用户说不同的语言，你应该选择能够最大限度支持各种语言需要的排序规则。比如，如果用户通常说西欧语言，就选择Latin1_General排序规则。
当你支持说不同语言的用户时，最重要的是为所有字符数据使用Unicode数据类型如nchar,nvarchar,nvarchar(max)。Unicode避免了非Unicode数据类型如char,varchar,text的code page转换难题。当你为所有列使用Unicode数据类型时，排序规则仍然是至关重要的，因为它定义了Unicode字符的比较次序和排序。即使你使用Unicode数据类型存储字符数据，你也应当选择支持大多数用户的排序规则，以免列或变量使用非Unicode数据类型实现。
SQL Server仅能支持底层操作系统已安装或支持的code page。当你执行依赖于排序规则的操作时，被参考对象所使用的SQL Server排序规则必需使用运行于该计算机上的操作系统支持或安装的code page。
Windows2000及以后的Windows系统支持SQL Server使用的所有code page。

五、 设置和改变排序规则

排序规则可以在分别在server,database,column,expression和identifier层级指定。当你安装SQL Server实例时，你为该实例指定默认的server级排序规则。每次你创建数据库，你可以为该数据库指定默认的排序规则。如果你没有指定排序规则，那么服务器实例的默认排序规则将作为该数据库的默认排序规则。无论何时你定义字符类型的列、变量或参数，都可以为该对象指定排序规则。若你没有指定，将使用数据库的默认排序规则创建该对象。

六、 应用范例

查看当前SQL Server Instance的排序规则设置
select serverproperty('collation') as CollationSetting
//Chinese_PRC_CI_AS

查看SQL Server Instance支持的排序规则列表
SELECT * FROM ::fn_helpcollations()

查看用户数据库的排序规则设置
select databaseproperty('jiradb', 'collation') as CollationSetting
// NULL (未指定，将应用server实例的设置)

select DATABASEPROPERTYEX('jiradb', 'collation') as CollationSetting
//Chinese_PRC_CI_AS

SELECT name, collation_name FROM sys.databases WHERE name = 'jiradb'
//Chinese_PRC_CI_AS

修改数据库的排序规则设置
ALTER DATABASE jiradb COLLATE Latin1_General_CS_AS

修改Column的排序规则设置
CREATE TABLE MyTable
  (PrimaryKey   int PRIMARY KEY,
   CharCol      varchar(10) COLLATE French_CI_AS NOT NULL
  )
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

在查询的ORDER BY子句中指定排序规则，覆盖server、database或column层级的默认排序规则
USE AdventureWorks2008R2;
GO
SELECT LastName FROM Person.Person
ORDER BY LastName
COLLATE Traditional_Spanish_ci_ai ASC;
GO

忽略重音符号
select *
from dbo.Restaurants
where Name collate SQL_Latin1_General_CP1_CI_AI like 'Cafe'
//返回结果集中将包含 'Cafe' 和 'Café'。

七、 使用Unicode数据

当你仅使用字符数据和代码页时，想要在一个数据库中以多种语言存储数据是难以管理的。想要为数据库找到一种代码页能够存储所有必需的语言特定的字符，同样也是困难的。此外，当被运行各种各样代码页的不同客户端读取或更新时，难以保证这些特殊字符被正确地翻译。支持国际化客户端的数据库应当总是使用Unicode数据类型而不是非Unicode数据类型。
随着Internet的快速发展，支持更多运行不同语言和地区的客户端计算机就显得更加重要。为字符数据类型选择一种能支持全世界范围内用户需要的所有字符的代码页，将是困难的。
在国际化数据库中管理字符数据的最容易的办法是，总是使用Unicode数据类型如nchar，nvarchar和nvarchar(max),而不使用对应的非Unicode类型如char，varchar和text。
Unicode是将code points映射到字符的标准。由于它被设计用来涵盖世界上所有语言的所有字符，因此不需要使用不同的代码页来处理不同的字符集。SQL Server支持3.2版本的Unicode标准。
如果使用国际化数据库的所有应用程序也使用Unicode而不是非Unicode变量，在系统中任何地方都不需要执行字符翻译。这些客户端将看到与所有其他客户端同样的数据。
SQL Server将列中的所有文本系统目录数据存储为Unicode数据类型。数据库对象如表，视图和存储过程的名称，都存储在Unicode数据类型的列中。这使得应用程序能够仅使用Unicode开发，从而帮助避免了与代码页转换相关的所有问题。

八、 Unicode字符的存储以及对性能的影响

SQL Server使用UCS-2编码方法存储Unicode数据。在此机制下，所有Unicode字符都使用2个字节存储。
Unicode与非Unicode字符数据在存储方式上的差异取决于非Unicode数据是否使用双字节字符集（DBCS）存储。所有非东亚语言和泰国语以单字节存储非Unicode字符。因此，以Unicode存储这些语言所占用的空间是使用指定的非Unicode代码页的2倍。另一方面，许多其他亚洲语言的代码页指定以双字节字符集（DBCS）存储字符。因而对这些语言而言，Unicode和非Unicode字符在存储上几乎没有差异。
Unicode数据对性能的影响是以下各种因素综合决定的：

• Unicode排序规则和非Unicode排序规则之间的差异
• 双字节字符与单字节字符在排序上的差异
• 客户端与服务器之间的代码页转换

对于定义了Windows排序规则的非Unicode数据，SQL Server使用Unicode排序规则执行字符串比较。一般地，定义了Windows排序规则的非Unicode数据，在排序的性能上与Unicode数据几乎没有差异。
SQL Server使用非Unicode排序规则的唯一场合是定义了SQL排序规则的非Unicode数据。这种情况下，排序和扫描通常比应用Unicode排序规则更快速。Unicode排序规则应用于所有Unicode数据，无论定义使用Windows排序规则还是SQL排序规则。
其次，大量数据的排序，Unicode可能比非Unicode要慢，因为数据以双字节存储。另一方面，对亚洲语言字符而言，排序Unicode存储的数据比排序DBCS方式存储的数据要快速，因为DBCS数据实际上是单字节和双字节宽度的混合，而Unicode字符则是固定宽度的（2个字节）。
其他的性能问题主要决定于SQL Server实例与客户端之间的编码机制转换问题。一般地，client/server代码页转换对性能的影响可以说是微不足道的。
大多数时候，决定将数据即使是non-DBCS数据存储为Unicode，应当更多的是基于业务需要而非性能考虑。在Internet快速发展的全球化经济时代，支持运行不同语言与区域的客户端计算机变得比以往任何时候更加重要。此外，选择一种能支持全世界范围内用户需要的所有字符的代码页，变得日益困难。

参考
http://technet.microsoft.com/en-us/library/ms187582(v=sql.105).aspx