包含データベースの照合順序

適用対象:SQL ServerAzure SQL Managed Instance

大文字と小文字の区別、アクセント記号の区別、使用されるベース言語など、さまざまなプロパティがテキスト データの並べ替え順序と等値セマンティクスに影響します。 これらの性質の指定は、データの照合順序の選択を通じて、SQL Server に示されます。 照合順序自体の詳細については、「照合順序 と Unicode のサポート」を参照してください。

照合順序は、ユーザー テーブルに格納されているデータだけでなく、メタデータ、一時オブジェクト、変数名など、SQL Server によって処理されるすべてのテキストに適用されます。これらの処理は、包含データベースと非包含データベースとでは異なります。 この変更は多くのユーザーには影響しませんが、インスタンスの独立性と均一性を提供するのに役立ちます。 ただし、これにより、包含データベースと非包含データベースの両方にアクセスするセッションで混乱や問題が発生する可能性もあります。

包含データベースの照合順序の動作は、非包含データベースでの動作とは微妙に異なります。 この動作は、インスタンスに対する独立性と簡易性を提供し、一般的には有益です。 一部のユーザーは、特にセッションが包含データベースと非包含データベースの両方にアクセスする場合に、問題が発生する可能性があります。

この記事では、変更の内容を明確にし、変更によって問題が発生する可能性がある領域について説明します。

非包含データベース

すべてのデータベースには既定の照合順序があります (データベースの作成時または変更時に設定できます)。 この照合順序は、データベース内のすべてのメタデータと、データベース内のすべての文字列列の既定値に使用されます。 ユーザーは、COLLATE 句を使用して、特定の列に対して別の照合順序を選択できます。

例 1

たとえば、北京で作業する場合は、中国語の照合順序を使用するでしょう。

ALTER DATABASE MyDB
    COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

列を作成する場合、既定の照合順序はこの中国語の照合順序ですが、必要に応じて別の照合順序を選択できます。

CREATE TABLE MyTable
(
    mycolumn1 NVARCHAR,
    mycolumn2 NVARCHAR COLLATE Frisian_100_CS_AS
);
GO

SELECT name, collation_name
FROM sys.columns
WHERE name LIKE 'mycolumn%';
GO

結果セットは次のとおりです。

name            collation_name
--------------- ----------------------------------
mycolumn1       Chinese_Simplified_Pinyin_100_CI_AS
mycolumn2       Frisian_100_CS_AS

これは比較的簡単に見えますが、いくつかの問題が発生します。 列の照合順序はテーブルが作成されるデータベースに依存するため、 tempdbに格納されている一時テーブルの使用に問題が発生します。 tempdbの照合順序は、通常、インスタンスの照合順序と一致します。データベースの照合順序と一致する必要はありません。

例 2

たとえば、前に示した (中国語) データベースを、 Latin1_General 照合順序を持つインスタンスで使用した場合を考えてみます。

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

一見すると、これら 2 つのテーブルは同じスキーマを持っているように見えますが、データベースの照合順序が異なるため、値に互換性がありません。

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

結果セットは次のとおりです。

メッセージ 468、レベル 16、状態 9、行 2

"Latin1_General_100_CI_AS_KS_WS_SC" と "Chinese_Simplified_Pinyin_100_CI_AS" の等価演算における照合順序の競合を解決できません。

この問題は、一時テーブルの照合順序を明示的に指定することで解決できます。 SQL Server では、DATABASE_DEFAULT 句に COLLATE キーワードを指定することで、これを簡単に行うことができます。

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX) COLLATE DATABASE_DEFAULT);
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

このクエリは、エラーなしで実行されるようになりました。

変数に関しても、照合順序に依存する動作があります。 次のような関数があるとします。

CREATE FUNCTION f (@x INT)
RETURNS INT
AS
BEGIN
    DECLARE @I AS INT = 1;
    DECLARE @İ AS INT = 2;
    RETURN @x * @i;
END

これは少し変わった関数です。 大文字と小文字を区別する照合順序では、return 句の @i を @I または @İ にバインドすることはできません。 大文字と小文字を区別しない Latin1_General 照合順序では、@i が @I にバインドされ、関数は 1 を返します。 しかし、大文字と小文字を区別しない Turkish 照合順序では、@i が @İ にバインドされ、関数は 2 を返します。 このことは、照合順序が異なるインスタンス間で移動されるデータベースでは、大きな問題になります。

包含データベース

包含データベースの設計目標は、データベースを自己完結させることなので、インスタンスと tempdb の照合順序への依存は解消する必要があります。 そのために、包含データベースにはカタログ照合順序の概念が導入されました。 カタログ照合順序は、システム メタデータと一時的なオブジェクトに使用されます。 詳細は以下のとおりです。

包含データベースでは、カタログの照合順序は Latin1_General_100_CI_AS_WS_KS_SC。 この照合順序は、SQL Server のすべてのインスタンス上のすべての包含データベースで同じであり、変更することはできません。

データベースの照合順序は保持されますが、ユーザー データの既定の照合順序としてのみ使用されます。 既定では、データベースの照合順序は model データベースの照合順序と同じですが、ユーザーが CREATE または ALTER DATABASE コマンドを使用して、非包含データベースと同様に変更できます。

新しいキーワード CATALOG_DEFAULT を COLLATE 句で使用できます。 これは、包含データベースと非包含データベースの両方のメタデータにおける現在の照合順序へのショートカットとして使用されます。 つまり、非包含データベースでは、メタデータがデータベース照合順序で照合されるため、 CATALOG_DEFAULT は現在のデータベース照合順序を返します。 包含データベースでは、ユーザーがデータベースの照合順序を変更してカタログの照合順序と一致しないようにできるため、これら 2 つの値が異なる場合があります。

以下の表は、非包含データベースと包含データベースのさまざまなオブジェクトの動作をまとめたものです。

前に説明した一時テーブルの例でわかるように、この照合順序の動作によって、多くの一時テーブルでは明示的に COLLATE 句を使用する必要がなくなります。 包含データベースでは、データベースとインスタンスの照合順序が異なる場合でも、このコードはエラーにならずに実行されます。

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

このクエリは、 T1_txt と T2_txt の両方が包含データベースのデータベース照合順序で照合されるために機能します。

包含コンテキストと非包含コンテキスト間のクロス

包含データベース内のセッションが包含されたままである限り、そのセッションは接続されているデータベース内で維持されます。 この場合、動作は簡単です。 しかし、セッションが包含コンテキストと非包含コンテキスト間にまたがると、2 つの規則のセットに対応する必要があるので、動作はより複雑になります。 これは、ユーザーが別のデータベースに USE する可能性があるため、部分的に包含されたデータベースで発生する可能性があります。 この場合、照合順序の規則の違いは、以下の原則に従って処理されます。

• バッチの照合順序の動作は、バッチを開始したデータベースによって決定されます。

この決定は、最初の USEを含め、コマンドが発行される前に行われます。 つまり、バッチが包含データベースで始まるが、最初のコマンドが非包含データベースに USE 場合、包含照合順序の動作はバッチに引き続き使用されます。 このシナリオでは、たとえば変数への参照に複数の結果が考えられる場合があります。

• 参照で正確に 1 つの一致が見つかることがあります。 この場合、参照はエラーなしで動作します。

• 参照で、以前に存在していた現在の照合順序で一致するものが見つからない場合があります。 これにより、明らかに作成されたにもかかわらず、変数が存在しないことを示すエラーが発生します。

• この参照では、本来は異なる複数の一致が見つかる場合があります。 これにより、エラーも発生します。

これをいくつかの例で示します。 これらの場合、データベースの照合順序が既定の照合順序 (MyCDB) に設定された、Latin1_General_100_CI_AS_WS_KS_SCという名前の部分的に包含されたデータベースがあると仮定します。 インスタンスの照合順序が Latin1_General_100_CS_AS_WS_KS_SCされていると仮定します。 2 つの照合順序の違いは、大文字と小文字を区別するかどうかだけです。

例 1

次の例は、参照がただ 1 つの一致を見つける場合を示しています。

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #a VALUES (1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

結果セットは次のとおりです。

x
-----------
1

この例では、識別された #a が、大文字と小文字を区別しないカタログ照合順序と、大文字と小文字を区別するインスタンス照合順序の両方でバインドされ、コードは正常に動作します。

例 2

次の例は、参照が、以前に存在していた現在の照合順序で一致するものが見つからない場合を示しています。

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #A VALUES (1);
GO

ここでは、大文字と小文字を区別しない既定の照合順序で #A が #a にバインドされ、挿入が機能し、

結果セットは次のとおりです。

(1 row(s) affected)

しかし、スクリプトを続行すると ...

USE master;
GO

SELECT * FROM #A;
GO

大文字と小文字を区別するインスタンスの照合順序で #A にバインドしようとして、エラーになります。

結果セットは次のとおりです。

Msg 208, Level 16, State 0, Line 2
Invalid object name '#A'.

例 3

次の例は、参照が本来は区別可能であった複数の一致を見つける場合を示しています。 まず、tempdb 内 (インスタンスと同様に、大文字と小文字を区別する照合順序) から、以下のステートメントを実行します。

USE tempdb;
GO

CREATE TABLE #a (x INT);
GO

CREATE TABLE #A (x INT);
GO

INSERT INTO #a VALUES (1);
GO

INSERT INTO #A VALUES (2);
GO

この照合順序ではテーブルが異なるため、このクエリは成功します。

結果セットは次のとおりです。

(1 row(s) affected)
(1 row(s) affected)

しかし、包含データベース内に移動すると、これらのテーブルへのバインドができなくなります。

USE MyCDB;
GO

SELECT * FROM #a;
GO

結果セットは次のとおりです。

Msg 12800, Level 16, State 1, Line 2
The reference to temp table name #a is ambiguous and cannot be resolved. Possible candidates are #a and #A.

関連コンテンツ

• 包含データベース