COLLATE （Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics分析平台系统 （PDW）Microsoft Fabric 中的仓库Microsoft Fabric 中的 SQL 数据库

定义数据库或表列的排序规则，或应用于字符串表达式时的排序规则强制转换操作。 排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。 如果创建数据库期间未指定，则会为数据库分配 SQL Server 实例的默认排序规则。 如果创建表列期间未指定，则会为该列分配数据库的默认排序规则。

Transact-SQL 语法约定

语法

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 Server 排序规则名称的排序规则名称。

注意

在 Microsoft Fabric 数据仓库中，COLLATE 语句中的 SELECT 子句不受限制，并且可以使用不受支持的排序规则应用。 在 Microsoft Fabric 数据仓库中，默认情况下，所有仓库都配置为区分大小写（CS）排序规则 Latin1_General_100_BIN2_UTF8。 还可以 创建不区分大小写的仓库（CI）排序规则Latin1_General_100_CI_AS_KS_WS_SC_UTF8。

但是，只有支持的排序规则可以与 COLLATE、CREATE TABLE、ALTER TABLE ADD nullable column和 SELECT INTO （CTAS） 语句中的 CREATE TABLE AS SELECT 子句一起使用。

database_default 使该 COLLATE 条款继承当前数据库的排序。

备注

该 COLLATE 条款可以在多个层级上加以规范。 这些功能包括以下这些：

  1. 创建或更改数据库。

    你可以用 COLLATE or ALTER DATABASE 语句的子句CREATE DATABASE来指定数据库的默认排序方式。 还可以在使用 SQL Server Management Studio 创建数据库时指定排序规则。 如果未指定排序规则，则会为数据库分配 SQL Server 实例的默认排序规则。

    注意

    Windows Unicode 仅限于该子句的排序，只能与子句一起使用COLLATE，对列级和表达式级数据的 nchar、nvarchar 和 ntext 数据类型应用对齐;这些排序不能与子句一起使用COLLATE来定义或更改数据库或服务器实例的整合。

  2. 创建或更改表列。

    你可以用 COLLATE or ALTER TABLE 语句的从句CREATE TABLE为每个字符字符串列指定校对。 还可以在使用 SQL Server Management Studio 创建表时指定排序规则。 如果未指定排序规则，则会为该列分配数据库的默认排序规则。

    你也可以在条款中指定database_defaultCOLLATE临时表中的列使用当前用户数据库的默认汇总，而不是 tempdb。

  3. 转换表达式的排序规则。

    你可以用该 COLLATE 子句对某个排序应用字符表达式。 为字符文本和变量分配当前数据库的默认排序规则。 为列引用分配列的定义排序规则。

标识符的排序规则取决于其定义的级别。 为实例级对象（如登录名和数据库名）的标识符分配实例的默认排序规则。 为数据库对象（如表、视图和列名）的标识符分配数据库的默认排序规则。 例如，只有在区分大小写的排序规则的数据库中创建名称不同的两个表，但在不区分大小写的排序规则的数据库中可能不会创建。 有关详细信息，请参阅 Database Identifiers。

当连接上下文与某个数据库相关时，可以创建变量、GOTO 标签、临时存储过程和临时表，且当已将上下文切换到其他数据库时引用它们。 变量、GOTO 标签、临时存储过程和临时表的标识符位于服务器实例的默认排序规则中。

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

COLLATE使用collate_name指代用于表达式、列定义或数据库定义的 SQL Server 排序名称或Windows排序。 collation_name 只能是指定的 Windows_collation_name 或 SQL_collation_name，并且参数必须包含文本值。 collation_name 不能由变量或表达式表示。

排序规则一般由排序规则名称标识，安装过程中除外。 在安装过程中，应该为 Windows 排序规则指定根排序规则指示符（排序规则区域设置），然后指定区分或不区分大小写或重音的排序选项。

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

SELECT name,
       description
FROM fn_helpcollations();

SQL Server 只支持由基础操作系统支持的代码页。 在执行取决于排序规则的操作时，引用的对象所使用的 SQL Server 排序规则必须使用计算机上运行的操作系统所支持的代码页。 这些操作可能包括：

  • 当创建或更改数据库时，为数据库指定默认排序规则。
  • 当创建或更改表时，为列指定排序规则。
  • 还原或附加数据库时，操作系统必须支持数据库的默认排序规则，并支持数据库中的任何 char、varchar 和 text 列或参数的排序规则。

注意

char 和 varchar 数据类型支持代码页转换，但是 text 数据类型不支持 。 不会报告代码页翻译期间的数据丢失。

如果被引用的对象所使用或指定的排序规则使用 Windows 不支持的代码页，则 SQL Server 会显示错误。

示例

A. 在 SELECT 期间指定排序规则

下面的示例创建一个简单表并插入 4 行。 然后，该示例在从表中选择数据时应用了两个排序规则，演示 Chiapas 如何以不同方式排序。

CREATE TABLE Locations
(
    Place VARCHAR (15) NOT NULL
);

GO

INSERT Locations (Place)
VALUES ('Chiapas'),
('Colima'),
('Cinco Rios'),
('California');
GO

--Apply a typical collation
SELECT Place
FROM Locations
ORDER BY Place COLLATE Latin1_General_CS_AS_KS_WS ASC;
GO

-- Apply a Spanish collation
SELECT Place
FROM Locations
ORDER BY Place COLLATE Traditional_Spanish_ci_ai ASC;
GO

-- Using LIKE
SELECT Place FROM Locations
WHERE Place COLLATE Latin1_General_CI_AS LIKE 'C%' ASC;
GO

下面是第一个 SELECT 查询的结果。

Place
-------------
California
Chiapas
Cinco Rios
Colima

下面是第二个 SELECT 查询的结果。

Place
-------------
California
Cinco Rios
Colima
Chiapas

下面是第三个 SELECT 查询的结果。

Place
-------------
Chiapas
Colima
Cinco Rios
California

B. 其他示例

有关使用 COLLATE、的更多示例，请参见 CREATE DATABASE 和 ALTER TABLE。

相关内容

  • Last updated on