这是我参与8月更文挑战的第10天，活动详情查看：8月更文挑战

排序规则

Collations 和 Case Sensitivity：排序规则和大小写敏感。通常在查询和文本使用中，我们关心的都是大小写的问题，区分大小写，在一般意义，应该叫排序规则（collations，或 collate）。

在相关ORM框架中，对于区分大小写的数据库，可能大多会使用string.Lower强制进行不区分大小写的比较。但这样做可能会妨碍应用程序使用索引…

比如 EF Core 5.0 开始，引入了对区分大小写或排序规则的配置。可以在不影响查询性能的情况下，高效的实现大小写是否区分的比较。

排序规则介绍

文本处理中的一个基本概念是排序规则（collation）。排序规则是一组规则，用于确定文本值如何排序和相等比较。

例如，不区分大小写的排序规则为了相等比较的目的，会忽略大写和小写字母之间的差异，但区分大小写的排序规则不会。

但是，由于大小写敏感是文化敏感的（例如，i 和 I 在土耳其语中代表不同的字母），因此存在多个不区分大小写的排序规则，每个都有自己的一套规则。

排序规则的范围也超出了区分大小写的范围，扩展到字符数据的其他方面；例如，在德语中，有时（但并非总是）希望将 ä 和 ae 视为相同。

最后，排序规则还定义了文本值如何排列顺序：德语将 ä 放在 a 之后，瑞典语将其放在字母表的末尾。

数据库中的所有文本操作都会使用一种排序规则（无论是显式还是隐式），用以确定操作如何比较和排序字符串。实际的可用排序规则及其命名方案是特定于数据库的。

数据库通常允许在数据库或列级别定义默认排序规则，并且，也可以明确指定查询中的特定操作应使用哪种排序规则。

创建一个存放ABab的数据表。后续操作基于此表演示。
CREATE TABLE OrderTest(
  letter char(1) NOT NULL
);
INSERT INTO OrderTest values('B'),('b'),('A'),('a');
复制代码

SQL Server的排序规则

排序规则的选用

在大多数数据库系统中，默认排序规则是在数据库级别定义的；除非被覆盖，否则该排序规则隐式适用于该数据库中发生的所有文本操作。

数据库排序规则通常在数据库创建时设置（通过 CREATE DATABASE DDL 语句），如果未指定，则默认为在设置时使用服务器级的值（server-level，即RDBMS或操作系统设置使用的collation）。

例如，SQL Server 中默认的服务器级排序规则是 SQL_Latin1_General_CP1_CI_AS，它是一种不区分大小写（case-insensitive）、区分重音（accent-sensitive）的排序规则。区分大小写可以使用SQL_Latin1_General_CP1_CS_AS排序规则。

尽管数据库系统通常允许更改现有数据库的排序规则，但这样做会导致复杂化；建议在创建数据库之前选择排序规则。

查看当前实例配置的排序规则

使用 SERVERPROPERTY 函数可以查询当前SQL Server实例的服务器排序规则。

SELECT CONVERT(varchar, SERVERPROPERTY('collation'));

-- 或者：EXECUTE sp_helpsort;

-- (No column name)
-- Chinese_PRC_CI_AS
复制代码

或属性中：

获取当前支持的collation

查询所有可用的排序规则，可以使用 fn_helpcollations() 内建函数：

SELECT * FROM sys.fn_helpcollations();
复制代码

排序规则的层级

SQL Server支持在以下级别设置排序规则：

Server-level collations
Database-level collations
Column-level collations
Expression-level collations

数据库的排序规则

在创建新数据库或数据库列时指定所需的排序规则是比较好的方法，而不是更改 SQL Server 实例的默认排序规则。

创建数据库时指定排序规则

CREATE DATABASE Collate_DB_Test
COLLATE Chinese_PRC_CS_AS;
复制代码

修改数据库的排序规则

ALTER DATABASE Collate_DB_Test COLLATE Chinese_PRC_CI_AS;
复制代码

查询数据库的排序规则

通过下面类似的语句，可以查看数据库的排序规则：

SELECT CONVERT (VARCHAR(50), DATABASEPROPERTYEX('database_name','collation'));
复制代码

或者从系统视图 sys.databases 中获取：

SELECT name, collation_name FROM sys.databases;  
复制代码

除此之外，还可以使用SSMD的属性查看数据库collation。但是无法通过生成创建数据库的语句看到collation。

例如：

SELECT CONVERT (VARCHAR(50), DATABASEPROPERTYEX('Collate_DB_Test','collation'));

-- Chinese_PRC_CS_AS
复制代码

列排序规则

排序规则也可以在文本列（text columns）上定义，覆盖数据库默认值。如果某些列需要不区分大小写，而数据库的其余部分需要区分大小写，这会很有用。

指定列的排序规则

Create TABLE myTable (
    id int primary key,
    mycol NVARCHAR(10) COLLATE Chinese_PRC_CI_AS
);
复制代码

修改列的排序规则

ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Chinese_Simplified_Pinyin_100_CS_AI;
复制代码

查看列的排序规则

列的排序规则无法通过创建表语句中显示出来。只能在SSMS的属性或下面的语句，查看collation

SELECT name, collation_name FROM sys.columns WHERE name = N'<insert character data type column name>';
复制代码

比如，join连接sys.columns和sys.tables表，查看排序规则

SELECT t.name TableName, c.name ColumnName, collation_name  
FROM sys.columns c  
inner join sys.tables t on c.object_id = t.object_id;  
复制代码

指定表的指定列的collation：

SELECT t.name TableName, c.name ColumnName, collation_name  
FROM sys.columns c  
inner join sys.tables t on c.object_id = t.object_id
where t.name='myTable' and c.name='mycol';  
复制代码

SQL语句中显式使用排序规则

条件子句中使用

在SQL语句中指定COLLATE关键字及排序规则名，其他部分和正常SQL一样保持不变，即可显式使用Collation。

<column_name/constant_value> COLLATE <collate_name>
复制代码

比如查询OrderTest表的列名，指定区分大小写的排序规则：

-- 默认不区分大小写
select * from OrderTest where letter='a';
复制代码

结果为：

letter
A
a
复制代码

-- 指定查询使用区分大小写的排序规则
select * from OrderTest where letter COLLATE SQL_Latin1_General_CP1_CS_AS ='a';

-- 或 Chinese_PRC_CS_AS
select * from OrderTest where letter COLLATE Chinese_PRC_CS_AS ='a';
复制代码

由于指定的排序规则区分大小写，结果中只会返回a

letter
a
复制代码

排序时指定collation

在查询的 Order By 子句中指定排序规则进行排序：

-- 默认 Chinese_PRC_CI_AS
select * from OrderTest order by letter;

-- 结果
-- letter
-- A
-- a
-- B
-- b
复制代码

指定区分大小写排序规则：

select * from OrderTest order by letter COLLATE Chinese_PRC_CS_AS;

-- 结果
-- letter
-- a
-- A
-- b
-- B
复制代码

按照Unicode编码进行排序：

-- 按照Unicode码值排序
select * from OrderTest order by letter COLLATE Chinese_PRC_BIN2; -- 或者 COLLATE Chinese_PRC_BIN;

-- 结果
-- letter
-- A
-- B
-- a
-- b
复制代码

显示排序规则和索引

索引是数据库性能中最重要的因素之一 —— 使用索引高效运行的查询可能会在没有该索引的情况下停止执行。

索引隐式继承其列的排序规则；这意味着该列上的所有查询都自动使用在该列上定义的索引 —— 前提是该查询未指定不同的排序规则。

在查询中指定显式排序规则通常会阻止查询使用该列上定义的索引，因为排序规则将不再匹配（the collations would no longer match）；

因此，建议在使用此功能时谨慎行事。最好在列（或数据库）级别定义排序规则，允许所有查询隐式使用该排序规则并从索引中受益。

请注意，某些数据库允许在创建索引时定义排序规则（例如 PostgreSQL、Sqlite）。这允许在同一列上定义多个索引，加快具有不同排序规则的操作（例如区分大小写和不区分大小写的比较）。

始终检查查询的查询计划，并确保在对大量数据执行的性能关键查询中使用了正确的索引。

【重点】关于排序规则中常见的几种缩写的含义

关于SQL Server的Collation中，有很多缩写flag标识，下面简要介绍下含义。

这些索引在其他的collation中也是比较通用的。

_CS：Case-sensitive。区分大小写字母，小写字母将排在大写之前。不区分可以使用_CI——Case-insensitive。
_AS：Accent-sensitive。区分重音字符和非重音字符。例如，“a”不等于“ấ”。如果不设置此项，则排序规则不区分重音。可以显示选择_AI——Accent-insensitive。
_BIN：Binary。根据为每个字符定义的位模式对 SQL Server 表中的数据进行排序和比较。

二进制排序顺序区分大小写和重音。二进制也是最快的排序顺序。

_WS：Width-sensitive。区分全角和半角字符（full-width and half-width characters）。如果未选择此项，SQL Server会将同一字符的全角和半角表示视为相同以进行排序。省略此项是指定全角半角不敏感的唯一方法。
_BIN2：Binary-code point。根据 Unicode 数据的 Unicode 代码点对 SQL Server 表中的数据进行排序和比较。对于非 Unicode 数据，二进制码点使用与二进制排序相同的比较。

使用二进制码点排序顺序的优点是，在比较排序 SQL Server 数据的应用程序中不需要数据重新排序。因此，

二进制码点排序顺序提供了更简单的应用程序开发和可能的性能提升。Binary collations

_UTF8：UTF-8。允许将 UTF-8 编码的数据存储在SQL Server中。如果未选择此选项，SQL Server 将对适用的数据类型使用默认的非 Unicode 编码格式。
_KS：Kana-sensitive。区分两种类型的日语假名字符：平假名和片假名。如果不设置此项，则排序规则不区分假名。省略此项是指定假名不敏感的唯一方法。
_VSS：Variation-selector-sensitive。区分 SQL Server 2017 (14.x) 中引入的日语排序规则 Japanese_Bushu_Kakusu_140 和 Japanese_XJIS_140 中的各种表意变体选择器。变体序列由一个基本字符和一个附加变体选择器组成。如果未选择此 _VSS 项，则排序规则对变体选择器不敏感，并且在比较中不考虑变体选择器。

supplementary character补充字符_SC

SQL Server 2012 (11.x) 引入了一系列新的补充字符 (_SC——supplementary character) 排序规则，可与 nchar、nvarchar 和 sql_variant 数据类型一起使用以表示完整的 Unicode 字符范围 (000000–10FFFF)。例如：Latin1_General_100_CI_AS_SC。

关于SQL Server的字符集信息

sys.syscharsets视图中可以查看有关字符集的信息。

参考和推荐

主要参考自Collation and Unicode support
推荐查看下这篇文章介绍：Questions About SQL Server Collations You Were Too Shy to Ask，里面详细介绍了常见的和我们会关系的几个关于collation的问题，尤其还介绍了如何识别或查找 Unicode-only 的排序规则？，这些只能使用nchar, nvarchar 或 ntext 数据类型。

文章版权归作者所有，未经允许请勿转载。

THE END

后端