設定或變更資料行定序Set or Change the Column Collation

您可以透過為資料表中特定資料行指定不同的定序並使用下列其中一種方法,覆寫 charvarchartextncharnvarcharntext 資料的資料庫定序:You can override the database collation for char, varchar, text, nchar, nvarchar, and ntext data by specifying a different collation for a specific column of a table and using one of the following:

  • CREATE TABLEALTER TABLE的 COLLATE 子句。The COLLATE clause of CREATE TABLE and ALTER TABLE. 例如:For example:

    CREATE TABLE dbo.MyTable  
      (PrimaryKey   int PRIMARY KEY,  
       CharCol      varchar(10) COLLATE French_CI_AS NOT NULL  
      );  
    GO  
    ALTER TABLE dbo.MyTable ALTER COLUMN CharCol  
                varchar(10)COLLATE Latin1_General_CI_AS NOT NULL;  
    GO  
    
  • Transact-SQLSQL Server Management Studio的 COLLATE 子句。. 如需詳細資訊,請參閱 定序與 Unicode 支援For more information, Collation and Unicode Support.

  • 使用 管理物件 (SMO) 中的 Column.Collation SQL ServerSQL Server 屬性。Using the Column.Collation property in SQL ServerSQL Server Management Objects (SMO).

    如果目前下列任何一個項目參考資料行定序的話,就無法變更其定序:You cannot change the collation of a column that is currently referenced by any one of the following:

  • 計算資料行A computed column

  • 索引An index

  • 散發統計資料,不論是自動產生或由 CREATE STATISTICS 陳述式產生Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

  • CHECK 條件約束A CHECK constraint

  • FOREIGN KEY 條件約束A FOREIGN KEY constraint

    當您使用 tempdb時, COLLATE 子句會包含 database_default 選項,將暫存資料表中的資料行指定為使用連線的目前使用者資料庫預設定序,而非 tempdb的定序。When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.

定序與 text 資料行Collations and text Columns

您可以插入或更新 text 資料行的值,該資料行定序與資料庫預設定序的字碼頁不同。You can insert or update values in a text column whose collation is different from the code page of the default collation of the database. SQL ServerSQL Server 以隱含方式將該值轉換為資料行定序。 implicitly converts the values to the collation of the column.

定序與 tempdbCollations and tempdb

tempdb 資料庫在每次 SQL ServerSQL Server 啟動時建置,且預設定序與 model 資料庫相同。The tempdb database is built every time SQL ServerSQL Server is started and has the same default collation as the model database. 通常與執行個體的預設定序相同。This is typically the same as the default collation of the instance. 如果建立使用者資料庫,並指定與 model不同的預設定序,使用者資料庫的預設定序就會與 tempdb不同。If you create a user database and specify a different default collation than model, the user database has a different default collation than tempdb. 所有暫存預存程序或暫存資料表會在 tempdb中建立及儲存。All temporary stored procedures or temporary tables are created and stored in tempdb. 這表示暫存資料表中所有隱含的資料行,與暫存預存程序中所有可強迫的常數、變數與參數,都會與建在永久資料表和預存程序中的同等物件具有不同的定序。This means that all implicit columns in temporary tables and all coercible-default constants, variables, and parameters in temporary stored procedures have collations that are different from comparable objects created in permanent tables and stored procedures.

這將造成使用者自訂資料庫與系統資料庫物件之間的定序不相符。This could lead to problems with a mismatch in collations between user-defined databases and system database objects. 例如, SQL ServerSQL Server 執行個體使用 Latin1_General_CS_AS 定序,而您執行下列陳述式:For example, an instance of SQL ServerSQL Server uses the Latin1_General_CS_AS collation and you execute the following statements:

CREATE DATABASE TestDB COLLATE Estonian_CS_AS;  
USE TestDB;  
CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );  

在此系統中, tempdb 資料庫使用 Latin1_General_CS_AS 定序與字碼頁 1252,而 TestDBTestPermTab.Col1 使用 Estonian_CS_AS 定序與字碼頁 1257。In this system, the tempdb database uses the Latin1_General_CS_AS collation with code page 1252, and TestDB and TestPermTab.Col1 use the Estonian_CS_AS collation with code page 1257. 例如:For example:

USE TestDB;  
GO  
-- Create a temporary table with the same column declarations  
-- as TestPermTab  
CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );  
INSERT INTO #TestTempTab  
         SELECT * FROM TestPermTab;  
GO  

承上例, tempdb 資料庫使用 Latin1_General_CS_AS 定序,而 TestDBTestTab.Col1 則使用 Estonian_CS_AS 定序。With the previous example, the tempdb database uses the Latin1_General_CS_AS collation, and TestDB and TestTab.Col1 use the Estonian_CS_AS collation. 例如:For example:

SELECT * FROM TestPermTab AS a INNER JOIN #TestTempTab on a.Col1 = #TestTempTab.Col1;  

因為 tempdb 使用預設伺服器定序,而 TestPermTab.Col1 使用不同的定序,所以 SQL Server 會傳回此錯誤訊息:「無法解析等於作業中,'Latin1_General_CI_AS_KS_WS' 與 'Estonian_CS_AS' 之間的定序衝突」。Because tempdb uses the default server collation and TestPermTab.Col1 uses a different collation, SQL Server returns this error: "Cannot resolve collation conflict between 'Latin1_General_CI_AS_KS_WS' and 'Estonian_CS_AS' in equal to operation."

為避免此錯誤,您可以使用以下任一種替代方法:To prevent the error, you can use one of the following alternatives:

  • 指定暫存資料表的資料行使用使用者資料庫的預設定序,而不使用 tempdb的預設定序。Specify that the temporary table column use the default collation of the user database, not tempdb. 這使得暫存資料表可配合多個資料庫中格式類似的資料表 (如果系統有這樣的需求)。This enables the temporary table to work with similarly formatted tables in multiple databases, if that is required of your system.

    CREATE TABLE #TestTempTab  
       (PrimaryKey int PRIMARY KEY,  
        Col1 nchar COLLATE database_default  
       );  
    
  • #TestTempTab 資料行指定正確的定序:Specify the correct collation for the #TestTempTab column:

    CREATE TABLE #TestTempTab  
       (PrimaryKey int PRIMARY KEY,  
        Col1 nchar COLLATE Estonian_CS_AS  
       );  
    

另請參閱See Also

設定或變更伺服器定序 Set or Change the Server Collation
設定或變更資料庫定序 Set or Change the Database Collation
定序與 Unicode 支援 Collation and Unicode Support