COLLATE (Transact-SQL)COLLATE (Transact-SQL)

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

定义数据库或表列的排序规则,或应用于字符串表达式时的排序规则强制转换操作。Defines a collation of a database or table column, or a collation cast operation when applied to character string expression. 排序规则名称既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。Collation name can be either a Windows collation name or a SQL collation name. 如果创建数据库期间未指定,则会为数据库分配 SQL ServerSQL Server 实例的默认排序规则。If not specified during database creation, the database is assigned the default collation of the instance of SQL ServerSQL Server. 如果创建表列期间未指定,则会为该列分配数据库的默认排序规则。If not specified during table column creation, the column is assigned the default collation of the database.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

collation_name 应用于表达式、列定义或数据库定义的排序规则的名称。collation_name Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name 只能是指定的 Windows_collation_name 或 SQL_collation_name 。collation_name can be only a specified Windows_collation_name or a SQL_collation_name. collation_name 必须是文本值 。collation_name must be a literal value. collation_name 不能用变量或表达式表示 。collation_name cannot be represented by a variable or expression.

Windows_collation_name 是 Windows 排序规则名称的排序规则名称 。Windows_collation_name is the collation name for a Windows Collation Name.

SQL_collation_name 是 SQL Server 排序规则名称的排序规则名称 。SQL_collation_name is the collation name for a SQL Server Collation Name.

database_default 使 COLLATE 子句继承当前数据库的排序规则。database_default Causes the COLLATE clause to inherit the collation of the current database.

RemarksRemarks

可以在多个级别指定 COLLATE 子句。The COLLATE clause can be specified at several levels. 其中包括:These include the following:

  1. 创建或更改数据库。Creating or altering a database.

    可使用 CREATE DATABASEALTER DATABASE 语句的 COLLATE 子句指定数据库的默认排序规则。You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. 还可以在使用 SQL Server Management StudioSQL Server Management Studio 创建数据库时指定排序规则。You can also specify a collation when you create a database using SQL Server Management StudioSQL Server Management Studio. 如果不指定排序规则,则将为数据库分配 SQL ServerSQL Server 实例的默认排序规则。If you do not specify a collation, the database is assigned the default collation of the instance of SQL ServerSQL Server.

    备注

    Windows 的仅 Unicode 排序规则只能与 COLLATE 子句一起使用,将排序规则应用于列级和表达式级数据的 nchar、nvarchar 和 ntext 数据类型,而不能与 COLLATE 子句一起使用来定义或更改数据库或服务器实例的排序规则 。Windows Unicode-only collations can only be used with the COLLATE clause to apply collations to the nchar, nvarchar, and ntext data types on column-level and expression-level data; these cannot be used with the COLLATE clause to define or change the collation of a database or server instance.

  2. 创建或更改表列。Creating or altering a table column.

    可以使用 CREATE TABLEALTER TABLE 语句的 COLLATE 子句指定每个字符串列的排序规则。You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. 还可以在使用 SQL Server Management StudioSQL Server Management Studio 创建表时指定排序规则。You can also specify a collation when you create a table using SQL Server Management StudioSQL Server Management Studio. 如果不指定排序规则,将为列分配数据库的默认排序规则。If you do not specify a collation, the column is assigned the default collation of the database.

    还可使用 COLLATE 子句中的 database_default 选项,指定临时表中的列使用连接的当前用户数据库(而不是 tempdb)的默认排序规则 。You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

  3. 转换表达式的排序规则。Casting the collation of an expression.

    可以使用 COLLATE 子句将字符表达式应用于某个排序规则。You can use the COLLATE clause to apply a character expression to a certain collation. 为字符文本和变量分配当前数据库的默认排序规则。Character literals and variables are assigned the default collation of the current database. 为列引用分配列的定义排序规则。Column references are assigned the definition collation of the column.

标识符的排序规则取决于定义标识符的级别。The collation of an identifier depends on the level at which it is defined. 为实例级对象(如登录名和数据库名)的标识符分配实例的默认排序规则。Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. 为数据库对象(如表、视图和列名)的标识符分配数据库的默认排序规则。Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. 例如,对于名称差别仅在于大小写的两个表,可在使用区分大小写排序规则的数据库中创建,而不能在使用不区分大小写排序规则的数据库中创建。For example, two tables with names different only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation. 有关详细信息,请参阅 Database IdentifiersFor more information, see Database Identifiers.

当连接上下文与某个数据库相关时,可以创建变量、GOTO 标签、临时存储过程和临时表,且当已将上下文切换到其他数据库时引用它们。Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database. 变量、GOTO 标签、临时存储过程和临时表的标识符位于服务器实例的默认排序规则中。The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.

COLLATE 子句仅适用于 char、varchar、text、nchar、nvarchar 和 ntext 数据类型 。The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.

COLLATE 使用 collate_name 来引用 SQL ServerSQL Server 排序规则或 Windows 排序规则的名称,以应用于表达式、列定义或数据库定义 。COLLATE uses collate_name to refer to the name of either the SQL ServerSQL Server collation or the Windows collation to be applied to the expression, column definition, or database definition. collation_name 只能是指定的 Windows_collation_name 或 SQL_collation_name,并且参数必须包含文本值 。collation_name can be only a specified Windows_collation_name or a SQL_collation_name and the parameter must contain a literal value. collation_name 不能用变量或表达式表示 。collation_name cannot be represented by a variable or expression.

排序规则一般由排序规则名称标识,安装过程中除外。Collations are generally identified by a collation name, except in Setup. 在安装过程中,应该为 Windows 排序规则指定根排序规则指示符(排序规则区域设置),然后指定区分或不区分大小写或重音的排序选项。In Setup, you instead specify the root collation designator (the collation locale) for Windows collations, and then specify sort options that are sensitive or insensitive to case or accents.

可以执行系统函数 fn_helpcollations 来检索 Windows 排序规则和 SQL Server 排序规则的所有有效排序规则名称的列表:You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations:

SELECT name, description
FROM fn_helpcollations();

SQL ServerSQL Server 只支持由基础操作系统支持的代码页。can support only code pages that are supported by the underlying operating system. 在执行取决于排序规则的操作时,引用的对象所使用的 SQL ServerSQL Server 排序规则必须使用计算机上运行的操作系统所支持的代码页。When you perform an action that depends on collations, the SQL ServerSQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. 这些操作可能包括:These actions can include the following:

  • 当创建或更改数据库时,为数据库指定默认排序规则。Specifying a default collation for a database when you create or alter the database.
  • 当创建或更改表时,为列指定排序规则。Specifying a collation for a column when you create or alter a table.
  • 还原或附加数据库时,操作系统必须支持数据库的默认排序规则,并支持数据库中的任何 char、varchar 和 text 列或参数的排序规则 。When restoring or attaching a database, the default collation of the database and the collation of any char, varchar, and text columns or parameters in the database must be supported by the operating system.

备注

Azure SQL DatabaseAzure SQL Database 托管实例服务器排序规则为 SQL_Latin1_General_CP1_CI_AS ,且不能更改。managed instance server collation is SQL_Latin1_General_CP1_CI_AS and cannot be changed.

char 和 varchar 数据类型支持代码页转换,但是 text 数据类型不支持 。Code page translations are supported for char and varchar data types, but not for text data type. 不报告代码页转换过程中的数据丢失。Data loss during code page translations is not reported.

如果被引用的对象所使用或指定的排序规则使用 Windows 不支持的代码页,则 SQL ServerSQL Server 会显示错误。If the collation specified or the collation used by the referenced object uses a code page not supported by Windows, SQL ServerSQL Server displays an error.

示例Examples

A.A. 在执行 SELECT 过程中指定排序规则Specifying collation during a SELECT

下面的示例创建一个简单表并插入 4 行。The following example creates a simple table and inserts 4 rows. 然后,该示例在从表中选择数据时应用了两个排序规则,演示 Chiapas 如何以不同方式排序。Then the example applies two collations when selecting data from the table, demonstrating how Chiapas is sorted differently.

CREATE TABLE Locations
(Place varchar(15) NOT NULL);
GO
INSERT Locations(Place) VALUES ('Chiapas'),('Colima')
                             , ('Cinco Rios'), ('California');
GO
--Apply an 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

这里是第一个查询的结果。Here are the results from the first query.

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

这里是第二个查询的结果。Here are the results from the second query.

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

B.B. 其他示例Additional examples

有关使用 COLLATE 的其他示例,请参阅 CREATE DATABASE 示例 G. 创建数据库并指定排序规则名称和选项,以及 ALTER TABLE 示例 V. 更改列排序规则For additional examples that use COLLATE, see CREATE DATABASE example G. Creating a database and specifying a collation name and options, and ALTER TABLE example V. Changing column collation.

另请参阅See Also