さまざまなプロパティは、テキスト データの並べ替え順序と等価セマンティクス (大文字と小文字の区別、アクセントの区別、使用されている基本言語など) に影響します。 これらの品質は、データの照合順序の選択によって 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
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 では、COLLATE 句に DATABASE_DEFAULT キーワードを指定することで、これをやや簡単にします。
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
JOIN #T2
ON T1.T1_txt = #T2.T2_txt ;
これはエラーなしで実行されるようになりました。
また、変数を使用して照合順序に依存する動作を確認することもできます。 次の関数について考えてみましょう。
CREATE FUNCTION f(@x INT) RETURNS INT
AS BEGIN
DECLARE @I INT = 1
DECLARE @?? INT = 2
RETURN @x * @i
END;
これはかなり独特な機能です。 大文字と小文字を区別する照合順序では、return 句の @i を @I または @??. にバインドできません。 大文字と小文字を区別しないLatin1_General照合順序では、 @i は @Iにバインドされ、関数は 1 を返します。 ただし、大文字と小文字を区別しないトルコ語の照合順序では、 @i は @??, にバインドされ、関数は 2 を返します。 これにより、照合順序が異なるインスタンス間を移動するデータベースに大きな影響を与える可能性があります。
包含データベース
包含データベースの設計上の目的は、データベースを自己完結型にすることであるため、インスタンスと tempdb 照合順序への依存を切断する必要があります。 これを行うために、包含データベースではカタログ照合順序の概念が導入されています。 カタログ照合順序は、システム メタデータと一時的なオブジェクトに使用されます。 詳細については、以下を参照してください。
包含データベースでは、カタログの照合順序 Latin1_General_100_CI_AS_WS_KS_SC。 この照合順序は、SQL Server のすべてのインスタンス上のすべての包含データベースで同じであり、変更することはできません。
データベースの照合順序は保持されますが、ユーザー データの既定の照合順序としてのみ使用されます。 既定では、データベースの照合順序はモデル データベースの照合順序と同じですが、包含されていないデータベースと同様に、 CREATE または ALTER DATABASE コマンドを使用してユーザーが変更できます。
COLLATE句では、新しいキーワードCATALOG_DEFAULTを使用できます。 これは、包含データベースと非包含データベースの両方のメタデータの現在の照合順序へのショートカットとして使用されます。 つまり、非包含データベースでは、メタデータがデータベース照合順序で照合されるため、 CATALOG_DEFAULT は現在のデータベース照合順序を返します。 包含データベースでは、カタログの照合順序と一致しないようにデータベースの照合順序を変更できるため、これら 2 つの値が異なる場合があります。
非包含データベースと包含データベースの両方のさまざまなオブジェクトの動作を次の表にまとめます。
| アイテム | 非コンテインドデータベース | 包含データベース |
| ユーザー データ (既定) | データベース_デフォルト | データベース_デフォルト |
| 一時データ (既定値) | TempDB 照合順序 | データベース_デフォルト |
| メタデータ | DATABASE_DEFAULT/CATALOG_DEFAULT | カタログ_デフォルト |
| 一時メタデータ | tempdb 照合順序 | カタログ_デフォルト |
| 変数 | インスタンスの照合順序 | CATALOG_DEFAULT |
| ラベルに移動する | インスタンスの照合順序 | CATALOG_DEFAULT |
| カーソル名 | インスタンスの照合順序 | カタログ既定 |
前に説明した一時テーブルの例では、この照合順序の動作により、ほとんどの一時テーブルで明示的な COLLATE 句が不要になることがわかります。 包含データベースでは、データベースとインスタンスの照合順序が異なる場合でも、このコードがエラーなしで実行されるようになりました。
CREATE TABLE T1 (T1_txt nvarchar(max)) ;
GO
CREATE TABLE #T2 (T2_txt nvarchar(max));
GO
SELECT T1_txt, T2_txt
FROM T1
JOIN #T2
ON T1.T1_txt = #T2.T2_txt ;
これは、 T1_txt と T2_txt の両方が包含データベースのデータベース照合順序で照合されるために機能します。
包含コンテキストと非包含コンテキストの交差
包含データベース内のセッションが保持されている限り、セッションは接続先のデータベース内に残る必要があります。 この場合、動作は非常に簡単です。 ただし、セッションが包含コンテキストと非包含コンテキストの間で交差する場合、2 つのルール セットをブリッジする必要があるため、動作はより複雑になります。 これは、ユーザーが別のデータベースに USE する可能性があるため、部分的に包含されたデータベースで発生する可能性があります。 この場合、照合順序規則の違いは、次の原則によって処理されます。
- バッチの照合順序の動作は、バッチが開始されるデータベースによって決まります。
この決定は、最初の USEを含め、コマンドが発行される前に行われます。 つまり、バッチが包含データベースで始まり、最初のコマンドが非包含データベースに対する USE である場合、包含照合順序の動作はバッチに引き続き使用されます。 これを考えると、たとえば変数への参照には、複数の結果が考えられます。
参照は完全に 1 つの一致を見つける場合があります。 この場合、参照はエラーなしで動作します。
参照では、以前に存在していた現在の照合順序で一致するものが見つからない場合があります。 これにより、明らかに作成されたにもかかわらず、変数が存在しないことを示すエラーが発生します。
このリファレンスを使用すると、もともと異なっていた複数の一致が見つかることがあります。 これにより、エラーも発生します。
これをいくつかの例で説明します。 ここでは、データベースの照合順序が既定の照合順序 (Latin1_General_100_CI_AS_WS_KS_SC) に設定された、MyCDBという名前の部分包含データベースがあることを前提としています。 インスタンスの照合順序が 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 にバインドしようとするとエラーが発生します。
結果セットを次に示します。
メッセージ 208、レベル 16、状態 0、行 2
オブジェクト名 '#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
結果セットを次に示します。
メッセージ 12800、レベル 16、状態 1、行 2
一時テーブル名 '#a' への参照はあいまいであり、解決できません。 候補は "#a" と "#A" です。
結論
包含データベースの照合順序の動作は、非包含データベースの照合順序と微妙に異なります。 この動作は一般に有益であり、インスタンスに依存せずにシンプルになります。 一部のユーザーは、特にセッションが包含データベースと非包含データベースの両方にアクセスする場合に、問題が発生する可能性があります。