SQL之COLLATE 子句 排序规则

COLLATE 是一个子句，可应用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则转换。

语法

COLLATE { <collation_name> | database_default }
<collation_name> :: =
     { Windows_collation_name } | { SQL_collation_name }

参数

collation_name
    应用于表达式、列定义或数据库定义的排序规则的名称。collation_name 可以只是指定的Windows_collation_name 或 SQL_collation_name 。collation_name 必须是文字值。   collation_name 不能用变量或表达式表示。
    Windows_collation_name 是用于 Windows 排序规则名称的排序规则名称。
    SQL_collation_name 是用于 SQL 排序规则名称的排序规则名称。

database_default
    使 COLLATE 子句继承当前数据库的排序规则。

 

下面说明下什么是排序规则，SQL Server 2005 提供了两组排序规则：Windows 排序规则和 SQL 排序规则。

Windows 排序规则

Windows 排序规则是为 SQL Server 定义的排序规则，用来支持 Windows 区域设置。Windows 区域设置的数量比SQL Server Windows 排序规则多。Windows区域设置名称是基于语言和地域的。例如，法语（加拿大）。不过，若干种语言可以共享通用的字母表和规则进行字符的排序和比较。另外，Windows区域设置还指定 SQL Server Windows排序规则不具有的属性，如货币、日期和时间格式。因为不同的国家和地区（比如英国和美国）使用不同的货币、日期和时间格式，因此它们需要不同的Windows 排序规则。但由于它们使用相同的字母表和规则来对字符进行排序和比较，因而可以使用相同的 SQL Server 排序规则。

SQL 排序规则

SQL 排序规则是一种兼容性选项，用来匹配 SQL Server早期版本所指定的代码页号和排序顺序的公用组合属性。许多排序规则都支持通过后缀指定区分大小写、重音、假名、全半角，但不全是这样。在 SQLServer 2005 中，应优先采用 Windows 排序规则。特别是在数据库中 Unicode 列与非 Unicode列都存在时。Windows 排序规则实际上对 Unicode 数据和非 Unicode 数据都应用了基于 Unicode 的排序规则。这表示SQL Server 在内部将非 Unicode 数据转化为 Unicode 来执行比较操作。这保证了 SQL Server中所有数据类型的一致性，也使开发人员能够使用 SQL Server 中的排序规则来对字符串进行排序。另一方面，对 Unicode数据使用相应的 Windows 排序规则后，SQL 排序规则可以对非 Unicode 数据应用非 Unicode 排序规则，对 Unicode数据应用 Unicode 排序规则。这一差异可能导致对相同字符的比较得到不一致的结果。因此，如果您的数据库中既有 Unicode 列，又有非Unicode 列，应将它们都定义为使用 Windows 排序规则，这样可以对 Unicode 和非 Unicode 数据使用相同的排序规则。

注意：COLLATE 子句只能应用于 char、varchar、text、nchar、nvarchar 和 ntext 数据类型。

可以执行系统函数 fn_helpcollations 以检索 Windows 排序规则和 SQL 排序规则的所有有效排序规则名称的列表：

SELECT *
FROM fn_helpcollations()

下表说明 SQL Server 2005 的 Windows 排序规则排序顺序选项。

排序顺序（后缀） 排序顺序说明

二进制 (_BIN)¹

根据为每个字符定义的位模式对 SQL Server 表中的数据进行排序和比较。二进制排序顺序既区分大小写，也区分重音。二进制排序顺序的速度也最快。有关详细信息，请参阅使用二进制排序规则。

如果未选择此选项，则 SQL Server 将遵循字典中定义的相关语言或字母表的排序和比较规则。

二进制-码位 (_BIN2)¹

根据 Unicode 数据的 Unicode 码位对 SQL Server 表中的数据进行排序和比较。对于非 Unicode 数据，二进制-码位将使用与二进制排序相同的比较方式。

使用二进制-码位排序顺序的优点是：对已排序的 SQL Server 数据进行比较的应用程序不必重新对数据进行排序。因此，二进制-码位排序顺序使应用程序开发变得更加简单，从而可以提高性能。有关详细信息，请参阅使用二进制排序规则。

区分大小写 (_CS)

区分大写字母和小写字母。如果选择此项，排序时小写字母将在其对应的大写字母之前。

如果未选择此选项，则 SQL Server 认为字母的大小写形式对于排序目的而言是相同的。

区分重音 (_AS)

区分重音字符和非重音字符。例如，“a”和“?”将被视为不同的字符。

如果未选择此项，在排序时，SQL Server 将把字母的重音形式和非重音形式视为相同。

区分假名 (_KS)

区分日语中的两种假名字符类型：平假名和片假名。

如果未选择此选项，则 SQL Server 认为片假名字符和平假名字符对于排序目的而言是相等的。

区分全半角 (_WS)

区分字符的单字节形式和双字节形式。

如果未选择此项，在排序时，SQL Server 将把同一字符的单字节形式和双字节形式视为相同。

排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：
     Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀 含义：

_BIN 二进制排序
_CI(CS) 是否区分大小写，CI不区分，CS区分
_AI(AS) 是否区分重音，AI不区分，AS区分
_KI(KS) 是否区分假名类型,KI不区分，KS区分
_WI(WS) 是否区分宽度 WI不区分，WS区分

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