定序Collations

本主題適用於: 是SQL Server (從 2008 開始)是Azure SQL Database是Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

這是可套用至資料庫定義或資料行定義來定義定序,或套用至字元字串運算式來套用定序轉換的子句。Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


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

引數Arguments

sys.databasescollation_name
套用至運算式、資料行定義或資料庫定義之定序的名稱。Is the name of the collation to be applied to the expression, column definition, or database definition. sys.databases可以只指定Windows_collation_nameSQL_collation_namecollation_name can be only a specified Windows_collation_name or a SQL_collation_name. sys.databases必須是常值。collation_name must be a literal value. sys.databases無法由變數或運算式。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.

在資料庫定義層級套用定序時,僅限 Unicode 的 Windows 定序就無法搭配 COLLATE 子句使用。When applying a collation at the database definition level, Unicode-only Windows collations cannot be used with the COLLATE clause.

database_defaultdatabase_default
使 COLLATE 子句繼承目前資料庫的定序。Causes the COLLATE clause to inherit the collation of the current database.

備註Remarks

您可以在許多層級指定 COLLATE 子句。The COLLATE clause can be specified at several levels. 這些選項包括:These include the following:

  1. 建立或變更資料庫。Creating or altering a database.

    您可以利用 CREATE DATABASE 或 ALTER DATABASE 陳述式的 COLLATE 子句來指定資料庫的預設定序。You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. 您也可以在利用 Transact-SQLSQL Server Management Studio 來建立資料庫時指定定序。You can also specify a collation when you create a database using Transact-SQLSQL 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 子句來套用定序來ncharnvarchar,和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; they cannot be used with the COLLATE clause to change the collation of a database or server instance.

  2. 建立或變更資料表資料行。Creating or altering a table column.

    您可以利用 CREATE TABLE 或 ALTER TABLE 陳述式的 COLLATE 子句來指定每個字元字串資料行的定序。You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. 您也可以在利用 Transact-SQLSQL Server Management Studio 來建立資料表時指定定序。You can also specify a collation when you create a table using Transact-SQLSQL Server Management Studio. 如果您沒有指定定序,就會將資料庫的預設定序指派給資料行。If you do not specify a collation, the column is assigned the default collation of the database.

    您也可以使用database_defaultCOLLATE 子句中指定暫存資料表中的資料行使用目前的使用者資料庫預設定,而不是連接的選項tempdbYou 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 子句使用,可套用至只有charvarchar文字ncharnvarcharntext資料型別。The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.

    COLLATE 會使用collate_name以參考 SQL Server 定序或 Windows 定序套用至運算式、 資料行定義或資料庫定義的名稱。COLLATE uses collate_name to refer to the name of either the SQL Server collation or the Windows collation to be applied to the expression, column definition, or database definition. sys.databases可以只指定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. sys.databases無法由變數或運算式。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.

  • 當還原或附加資料庫、 資料庫的預設定序和任何定序charvarchar,和文字資料行或在資料庫中的參數必須支援的作業系統。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.

    字碼頁翻譯支援charvarchar資料類型,但不適用於文字資料型別。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. 在選取時指定定序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 (SQL Server TRANSACT-SQL )範例G.建立資料庫並指定定序名稱和選項,和ALTER TABLE (TRANSACT-SQL )範例V.變更資料行定序For additional examples that use COLLATE, see CREATE DATABASE (SQL Server Transact-SQL) example G. Creating a database and specifying a collation name and options, and ALTER TABLE (Transact-SQL) example V. Changing column collation.

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
定序與 Unicode 支援 Collation and Unicode Support
定序優先順序 (Transact-SQL) Collation Precedence (Transact-SQL)
常數 (TRANSACT-SQL ) Constants (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL) CREATE DATABASE (SQL Server Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
資料表 (TRANSACT-SQL )table (Transact-SQL)