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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是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_nameSQL_collation_namecollation_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_nameWindows 定序名稱的定序名稱。Windows_collation_name is the collation name for a Windows Collation Name.

SQL_collation_nameSQL 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 子句使用,以便將定序套用至資料行層級和運算式層級資料的 ncharnvarcharntext 資料類型。這些無法搭配 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 Identifiers>。For 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 子句僅適用於 charvarchartextncharnvarcharntext 資料類型。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_nameSQL_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.
  • 當還原或附加資料庫時,作業系統必須支援資料庫的預設定序及資料庫中任何 charvarchartext 資料行或參數的定序。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.

支援 charvarchar 資料類型的字碼頁轉換,但不支援 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. 然後範例會在從資料表選取資料時套用兩個定序,並且示範如何以不同的方式排序 ChiapasThen 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