包含数据库的排序规则

适用于：SQL ServerAzure SQL 托管实例

许多属性会影响文本数据的排序顺序和相等语义，包括区分大小写、区分重音以及所用的基本语言。 对于这些特性，可通过选择数据的排序规则来表示给 SQL Server。 有关排序规则本身的更深入的讨论，请参阅 排序规则和 Unicode 支持。

排序规则不仅适用于用户表中存储的数据，还适用于由 SQL Server 处理的所有文本，包括元数据、临时对象、变量名称等。在这些内容的处理方面，包含数据库和非包含数据库采用不同的方式。 此更改不会影响许多用户，但有助于提供实例独立性和统一性。 但这还可能导致一些混淆，并对访问独立数据库和非独立数据库的会话造成问题。

包含数据库中的排序规则行为与非包含数据库中的行为略有不同。 此行为通常是有益的，因为它可以提供独立而简单的实例。 某些用户可能会遇到问题，尤其是在会话访问受包含数据库与非受包含数据库时。

本文阐明了更改的内容，并检查更改可能导致问题的区域。

非包含数据库

所有数据库都具有默认排序规则（可在创建或更改数据库时设置）。 此排序规则用于数据库中的所有元数据，以及数据库中所有字符串列的默认值。 通过使用 COLLATE 子句，用户可为任何特定的列选择不同的排序规则。

示例 1

例如，如果我们在北京工作，则可能会使用中文排序规则：

ALTER DATABASE MyDB
    COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

现在，如果创建列，其默认排序规则是此中文排序规则，但如果需要，我们可以选择另一个排序规则：

CREATE TABLE MyTable
(
    mycolumn1 NVARCHAR,
    mycolumn2 NVARCHAR COLLATE Frisian_100_CS_AS
);
GO

SELECT name, collation_name
FROM sys.columns
WHERE name LIKE 'mycolumn%';
GO

结果集如下。

name            collation_name
--------------- ----------------------------------
mycolumn1       Chinese_Simplified_Pinyin_100_CI_AS
mycolumn2       Frisian_100_CS_AS

这看起来比较简单，但会引发几个问题。 由于列的排序规则依赖于在其中创建表的数据库，因此使用存储在 tempdb其中的临时表时出现问题。 通常排序规则与实例的排序规则 tempdb 匹配，该排序规则不必与数据库排序规则匹配。

示例 2

例如，在具有 Latin1_General 排序规则的实例上使用时，请考虑前面显示的（中文）数据库：

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

首先，这两个表看起来与架构相同，但由于数据库的排序规则不同，因此这些值不兼容：

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

结果集如下。

消息 468，级别 16，状态 9，第 2 行

无法解决“Latin1_General_100_CI_AS_KS_WS_SC”和“Chinese_Simplified_Pinyin_100_CI_AS”之间在等于运算中的排序规则冲突。

通过显式排列临时表可修复此问题。 SQL Server 通过为 COLLATE 子句提供 DATABASE_DEFAULT 关键字来简化操作。

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX) COLLATE DATABASE_DEFAULT);
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

此查询现在运行时没有错误。

我们还可以看到，变量的行为也依赖于排序规则。 请考虑以下函数：

CREATE FUNCTION f (@x INT)
RETURNS INT
AS
BEGIN
    DECLARE @I AS INT = 1;
    DECLARE @İ AS INT = 2;
    RETURN @x * @i;
END

这是一个相当特殊的函数。 在区分大小写的排序规则中，@i 在返回子句中不能绑定到 @I 或 @İ 的任意一个。 在不区分大小写的 Latin1_General 排序规则中，@i 绑定到 @I，并且该函数将返回 1。 而在不区分大小写的 Turkish 排序规则中，@i 绑定到 @İ，并且该函数将返回 2。 如果在采用不同排序规则的实例之间移动数据库，则会给数据库造成严重的破坏。

包含的数据库

由于包含数据库的设计目标是让自身实现独立，因此必须切断它们对实例和 tempdb 排序规则的依赖。 为此，包含数据库引入了目录排序规则的概念。 目录排序规则适用于系统元数据和临时对象。 提供详细信息，如下所示。

在包含数据库中，目录排序规则为 Latin1_General_100_CI_AS_WS_KS_SC。 对于 SQL Server 的所有实例上的所有包含的数据库，此排序规则相同，无法更改。

数据库排序规则将得到保留，但只能用作用户数据的默认排序规则。 默认情况下，数据库排序规则等同于 model 数据库排序规则，但在非包含数据库中，用户可通过 CREATE 或 ALTER DATABASE 命令更改数据库排序规则。

CATALOG_DEFAULT 子句中提供了一个新关键字 COLLATE。 此关键字用作包含数据库和非包含数据库中当前元数据排序规则的快捷方式。 也就是说，在非包含数据库中， CATALOG_DEFAULT 返回当前数据库排序规则，因为元数据在数据库排序规则中排序。 在包含数据库中，这两个值可能不同，因为用户可以更改数据库排序规则，使其与目录排序规则不匹配。

下表总结了非包含数据库和包含数据库中各个对象的行为：

通过上述临时表示例可以看出，此排序规则行为使大多数临时表不再需要使用显式 COLLATE 子句。 在包含数据库中，即使数据库和实例采用不同的排序规则，此代码现在也可以正常运行：

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

此查询有效，因为 T1_txt 和 T2_txt 都根据所含数据库的数据库排序规则进行了排序。

在已包含上下文和未包含上下文之间切换

只要包含数据库中的会话仍处于包含状态，就必须保留在它所连接到的数据库内。 在这种情况下，行为非常简单。 但是，如果会话跨越包含和非包含上下文，其行为就会变得比较复杂，因为必须将两组规则联系起来。 这可能发生在部分隔离的数据库中，因为用户可能USE到另一个数据库。 在此情况下，排序规则之间的差异按以下原则处理。

• 批处理的排序规则行为由开始执行批处理的数据库决定。

此决定是在发出任何命令之前做出的，包括初始 USE命令。 也就是说，如果批处理在包含数据库中开始，但第一个命令是 USE 非包含的数据库，则包含的排序规则行为仍用于批处理。 在这种情况下，对变量的引用可能会有多个结果：

• 引用可能正好找到一个匹配项。 在这种情况下，引用的工作原理没有错误。

• 引用在当前排序规则中可能找不到匹配项，因为该排序规则以前有一个匹配项。 这引发了一个错误，指示变量不存在，即使它显然已创建。

• 引用可能会找到原先不同的多个匹配项。 这也会引发错误。

我们用几个示例来说明这一点。 假设存在一个名为MyCDB的部分包含数据库，其数据库排序规则设置为默认排序规则Latin1_General_100_CI_AS_WS_KS_SC。 假设实例排序规则为 Latin1_General_100_CS_AS_WS_KS_SC. 两个排序规则的区别只在于是否区分大小写。

示例 1

下面的示例演示引用只找到一个匹配项的情况。

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #a VALUES (1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

结果集如下。

x
-----------
1

在此示例中，标识的 #a 同时绑定在不区分大小写的目录排序规则和区分大小写的实例排序规则中，代码可以正常运行。

示例 2

下面的示例演示了在当前排序规则中引用找不到匹配项的情况，而在之前的排序规则中是有匹配项的。

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #A VALUES (1);
GO

在此示例中，#A 绑定到不区分大小写的默认排序规则中的 #a，而且插入部分可正常运行，

结果集如下。

(1 row(s) affected)

但如果继续运行脚本...

USE master;
GO

SELECT * FROM #A;
GO

将显示一个错误，因为我们尝试绑定到区分大小写的实例排序规则中的 #A；

结果集如下。

Msg 208, Level 16, State 0, Line 2
Invalid object name '#A'.

示例 3

下面的示例演示引用找到多个原本不同的匹配项的情况。 首先，我们在 tempdb（与实例具有相同的区分大小写的排序规则）中开始并执行以下代码。

USE tempdb;
GO

CREATE TABLE #a (x INT);
GO

CREATE TABLE #A (x INT);
GO

INSERT INTO #a VALUES (1);
GO

INSERT INTO #A VALUES (2);
GO

此查询成功，因为表在此排序规则中不同：

结果集如下。

(1 row(s) affected)
(1 row(s) affected)

但是，一旦进入包含数据库，我们就会发现无法再绑定到这些表。

USE MyCDB;
GO

SELECT * FROM #a;
GO

结果集如下。

Msg 12800, Level 16, State 1, Line 2
The reference to temp table name #a is ambiguous and cannot be resolved. Possible candidates are #a and #A.

相关内容

• 包含的数据库