列の照合順序の設定または変更

[アーティクル]
05/23/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

char 型、varchar 型、text 型、nchar 型、nvarchar 型、および ntext 型のデータのデータベース照合順序は、テーブルの列ごとに異なる照合順序を指定し、次のいずれかを使用することでオーバーライドできます。

次の例で示すような、CREATE TABLE と ALTER TABLE の COLLATE 句。
- インプレース変換。 以下に定義されている既存のテーブルのいずれかについて検討してください。
```
-- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);

-- VARCHAR column is encoded the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);
```
  列をインプレース変換して UTF-8 を使用するには、必要なデータ型、および UTF-8 が有効な照合順序を設定する ALTER COLUMN ステートメントを実行します。
```
ALTER TABLE dbo.MyTable 
ALTER COLUMN CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8
```
  このメソッドは、実装は簡単ですが、大規模なテーブルやビジーなアプリケーションでは、ブロッキング操作として問題となる可能性があります。
- コピーと置換。 以下に定義されている既存のテーブルのいずれかについて検討してください。
```
-- NVARCHAR column is encoded in UTF-16 because a supplementary character enabled collation is used
CREATE TABLE dbo.MyTable (CharCol NVARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC);
GO

-- VARCHAR column is encoded using the Latin code page and therefore is not Unicode capable
CREATE TABLE dbo.MyTable (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI);
GO
```
  列を変換して UTF-8 を使用するには、新しいテーブルにデータをコピーします。このテーブルでは既に、ターゲット列は必要なデータ型であって、UTF-8 が有効な照合順序であり、古いテーブルを置き換えています。
```
CREATE TABLE dbo.MyTableNew (CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
GO
INSERT INTO dbo.MyTableNew 
SELECT * FROM dbo.MyTable;
GO
DROP TABLE dbo.MyTable;
GO
EXEC sp_rename 'dbo.MyTableNew', 'dbo.MyTable';
GO
```
  この方法は、インプレース変換よりはるかに高速ですが、多くの依存関係 (FK、PK、トリガー、DF) を持つ複雑なスキーマの処理や、データベースが使用されている場合は、テーブルの末尾の同期を計画的に行うことが必要です。
詳細については、「照合順序および Unicode のサポート」を参照してください。
SQL Server Management Studio。詳細については、「列の変更 (データベースエンジン)」を参照してください。
SQL Server 管理オブジェクト (SMO) の Column.Collation プロパティの使用。

次のいずれかから現在参照されている列は、照合順序を変更することはできません。

計算列
インデックス
自動的に生成された、または CREATE STATISTICS ステートメントによって生成された分布統計情報
CHECK 制約
FOREIGN KEY 制約

tempdbを操作する場合、 COLLATE 句に database_default オプションを指定することで、一時テーブルの列で、接続の現在のユーザーデータベースでの既定の照合順序を tempdbの照合順序の代わりに使用するように指定することもできます。

照合順序と text 列

データベースの既定の照合順序のコードページと異なる照合順序が設定された text 列では、値の挿入と更新が可能です。 SQL Server により、値がこの列の照合順序に暗黙的に変換されます。

照合順序と tempdb

tempdb データベースは、SQL Server が起動されるたびに作成され、model データベースと同じ既定の照合順序が設定されます。これは、通常、インスタンスの既定の照合順序と同じになります。ユーザーデータベースを作成して、 modelと異なる既定の照合順序を指定すると、そのユーザーデータベースでは tempdbと異なる既定の照合順序が使用されます。一時ストアドプロシージャや一時テーブルは、すべて tempdb内に作成および格納されます。その結果、一時テーブル内のすべての暗黙の列、および一時ストアドプロシージャ内で強制的に適用されるすべての既定の定数、変数、パラメーターでは、パーマネントテーブルやストアドプロシージャで作成される同等のオブジェクトとは異なる照合順序が指定されます。

このため、ユーザー定義データベースとシステムデータベースオブジェクトの照合順序の不一致による問題が発生する可能性があります。たとえば、SQL Server のインスタンスでは照合順序として Latin1_General_CS_AS が使用されていて、次のステートメントを実行するとします。

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

このシステムでは、 tempdb データベースにコードページ 1252 の Latin1_General_CS_AS 照合順序が使用され、 TestDB と TestPermTab.Col1 にコードページ 1257 の Estonian_CS_AS 照合順序が使用されることになります。次に例を示します。

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 照合順序が使用され、 TestDB と TestTab.Col1 では Estonian_CS_AS 照合順序が使用されます。次に例を示します。

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

tempdb ではサーバーの既定照合順序が使用され、 TestPermTab.Col1 では異なる照合順序が使用されるので、"Cannot resolve collation conflict between 'Latin1_General_CI_AS_KS_WS' and 'Estonian_CS_AS' in equal to operation. " (equal to 操作の 'Latin1_General_CI_AS_KS_WS' と 'Estonian_CS_AS' 間での照合順序の競合を解決できません。) というエラーが SQL Server から返されます。

このエラーを回避するには、次のいずれかを行います。

一時テーブル列で、 tempdbではなく、ユーザーデータベースの既定の照合順序を使用するように指定します。この措置によって、システムで必要とされる場合に、一時テーブルを複数のデータベース内で同様の形式が設定されたテーブルと併用できます。
```
CREATE TABLE #TestTempTab  
   (PrimaryKey int PRIMARY KEY,  
    Col1 nchar COLLATE database_default  
   );  
```

次のように、 #TestTempTab 列に正しい照合順序を指定します。

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

参照

サーバーの照合順序の設定または変更
 データベースの照合順序の設定または変更
 照合順序と Unicode のサポート

次の方法で共有

列の照合順序の設定または変更

照合順序と text 列

照合順序と tempdb

参照

フィードバック

その他のリソース