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

適用対象: ○SQL Server XAzure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

大文字と小文字の区別、アクセント記号の区別、使用されるベース言語など、さまざまなプロパティがテキスト データの並べ替え順序と等値セマンティクスに影響します。Various properties affect the sort order and equality semantics of textual data, including case sensitivity, accent sensitivity, and the base language being used. これらの性質の指定は、データの照合順序の選択を通じて、 SQL ServerSQL Server に示されます。These qualities are expressed to SQL ServerSQL Server through the choice of collation for the data. 照合順序の詳細については、「照合順序と Unicode のサポート」を参照してください。For a more in-depth discussion of collations themselves, see Collation and Unicode Support.

照合順序は、ユーザー テーブルに格納されているデータだけでなく、メタデータ、一時オブジェクト、変数名など、SQL ServerSQL Server によって処理されるすべてのテキストに適用されます。これらの処理は、包含データベースと非包含データベースとでは異なります。Collations apply not only to data stored in user tables, but to all text handled by SQL ServerSQL Server, including metadata, temporary objects, variable names, etc. The handling of these differs in contained and non-contained databases. この変更は、多くのユーザーには影響しませんが、インスタンスに対する独立性と統一性を提供する助けになります。This change will not affect many users, but helps provide instance independence and uniformity. これも、包含データベースと非包含データベースの両方にアクセスするセッションの問題と同様に、いくつかの混乱を生じさせる場合があります。But this may also cause some confusion, as well as problems for sessions that access both contained and non-contained databases.

このトピックでは、変更の内容を明確にし、変更が問題を引き起こす状況について詳しく説明します。This topic clarifies the content of the change, and examines areas where the change may cause problems.

非包含データベースNon-Contained Databases

すべてのデータベースには既定の照合順序があり、データベースの作成時または変更時に設定できます。All databases have a default collation (which can be set when creating or altering a database. この照合順序は、データベース内のすべての文字列型の列に対する既定値と同様に、データベース内のすべてのメタデータで使用されます。This collation is used for all metadata in the database, as well as the default for all string columns within the database. ユーザーは、 COLLATE 句を使用して、特定の列に対して別の照合順序を選択できます。Users can choose a different collation for any particular column by using the COLLATE clause.

例 1Example 1

たとえば、北京で作業する場合は、中国語の照合順序を使用するでしょう。For example, if we were working in Beijing, we might use a Chinese collation:

ALTER DATABASE MyDB COLLATE Chinese_Simplified_Pinyin_100_CI_AS;  

その場合、列を作成すると、既定の照合順序は中国語の照合順序になりますが、必要であれば別の照合順序を選択できます。Now if we create a column, its default collation will be this Chinese collation, but we can choose another one if we want:

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  

以下に結果セットを示します。Here is the result set.

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

これは比較的簡単に見えますが、いくつかの問題が発生します。This appears relatively simple, but several problems arise. 列の照合順序はテーブルを作成したデータベースに依存するので、 tempdbに格納されている一時テーブルを使用する場合に問題が発生します。Because the collation for a column is dependent on the database in which the table is created, problems arise with the use of temporary tables which are stored in tempdb. tempdb の照合順序は、通常、インスタンスの照合順序と一致し、データベースの照合順序と一致するとは限りません。The collation of tempdb usually matches the collation for the instance, which does not have to match the database collation.

例 2Example 2

たとえば、上の中国語のデータベースが、 Latin1_General 照合順序のインスタンス上で使用されるとします。For example, consider the (Chinese) database above when used on an instance with a Latin1_General collation:

CREATE TABLE T1 (T1_txt nvarchar(max)) ;  
GO  
CREATE TABLE #T2 (T2_txt nvarchar(max)) ;  
GO  

一見したところでは、これらの 2 つのテーブルは同じスキーマを持っているように見えます。しかし、データベースの照合順序が異なるため、値は実際には互換性がありません。At first glance, these two tables look like they have the same schema, but since the collations of the databases differ, the values are actually incompatible:

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

以下に結果セットを示します。Here is the result set.

メッセージ 468、レベル 16、状態 9、行 2Msg 468, Level 16, State 9, Line 2

等しい操作の "Latin1_General_100_CI_AS_KS_WS_SC" と "Chinese_Simplified_Pinyin_100_CI_AS" 間での照合順序の競合を解決できません。Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and Chinese_Simplified_Pinyin_100_CI_AS" in the equal to operation.

この問題は、一時テーブルの照合順序を明示的に指定することで解決できます。We can fix this by explicitly collating the temporary table. SQL ServerSQL Server では、 COLLATE 句に DATABASE_DEFAULT キーワードを提供することで、この処理を簡易にしています。makes this somewhat easier by providing the DATABASE_DEFAULT keyword for the COLLATE clause.

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 ;  

これで、エラーなしで実行できるようになりました。This now runs without error.

変数に関しても、照合順序に依存する動作があります。We can also see collation-dependent behavior with variables. 次のような関数があるとします。Consider the following function:

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

これは少し変わった関数です。This is a rather peculiar function. 大文字と小文字を区別する照合順序では、return 句の中の @i が @I にも @ İ にもバインドできません。In a case-sensitive collation, the @i in the return clause cannot bind to either @I or @İ. 大文字と小文字を区別しない Latin1_General 照合順序では、@i が @I にバインドされ、関数は 1 を返します。In a case-insensitive Latin1_General collation, @i binds to @I, and the function returns 1. しかし、大文字と小文字を区別しない Turkish 照合順序では、@i が @İ にバインドされ、関数は 2 を返します。But in a case-insensitive Turkish collation, @i binds to @İ, and the function returns 2. このことは、照合順序が異なるインスタンス間で移動されるデータベースでは、大きな問題になります。This can wreak havoc on a database that moves between instances with different collations.

包含データベースContained Databases

包含データベースの設計目標は、データベースを自己完結させることなので、インスタンスと tempdb の照合順序への依存は解消する必要があります。Since a design objective of contained databases is to make them self-contained, the dependence on the instance and tempdb collations must be severed. そのために、包含データベースにはカタログ照合順序の概念が導入されました。To do this, contained databases introduce the concept of the catalog collation. カタログ照合順序は、システム メタデータと一時的なオブジェクトに使用されます。The catalog collation is used for system metadata and transient objects. 詳細については、以下で説明します。Details are provided below.

包含データベースでは、カタログ照合順序は Latin1_General_100_CI_AS_WS_KS_SCです。In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. この照合順序は、 SQL ServerSQL Server のすべてのインスタンスのすべての包含データベースで共通であり、変更できません。This collation is the same for all contained databases on all instances of SQL ServerSQL Server and cannot be changed.

データベースの照合順序は保持されますが、ユーザー データの既定の照合順序としてのみ使用されます。The database collation is retained, but is only used as the default collation for user data. 既定では、データベースの照合順序は model データベースの照合順序と同じですが、ユーザーが CREATE または ALTER DATABASE コマンドを使用して、非包含データベースと同様に変更できます。By default, the database collation is equal to the model database collation, but can be changed by the user through a CREATE or ALTER DATABASE command as with non-contained databases.

COLLATE句では、新しいキーワード CATALOG_DEFAULT を使用できます。A new keyword, CATALOG_DEFAULT, is available in the COLLATE clause. これは、包含データベースと非包含データベースの両方のメタデータにおける現在の照合順序へのショートカットとして使用されます。This is used as a shortcut to the current collation of metadata in both contained and non-contained databases. つまり、非包含データベースでは、メタデータがデータベースの照合順序で照合されるので、 CATALOG_DEFAULT は現在のデータベースの照合順序を返します。That is, in a non-contained database, CATALOG_DEFAULT will return the current database collation, since metadata is collated in the database collation. 包含データベースでは、ユーザーがデータベースの照合順序をカタログ照合順序と一致しないように変更できるため、これらの 2 つの値は異なる場合があります。In a contained database, these two values may be different, since the user can change the database collation so that it does not match the catalog collation.

以下の表は、非包含データベースと包含データベースのさまざまなオブジェクトの動作をまとめたものです。The behavior of various objects in both non-contained and contained databases is summarized in this table:

アイテムItem 非包含データベースNon-Contained Database 包含データベースContained Database
ユーザー データ (既定値)User Data (default) COLLATEDATABASE_DEFAULT COLLATEDATABASE_DEFAULT
一時データ (既定値)Temp Data (default) TempDB の照合順序TempDB Collation COLLATEDATABASE_DEFAULT
メタデータMetadata DATABASE_DEFAULT / CATALOG_DEFAULTDATABASE_DEFAULT / CATALOG_DEFAULT COLLATECATALOG_DEFAULT
一時的なメタデータTemporary Metadata TempDB の照合順序tempdb Collation COLLATECATALOG_DEFAULT
変数:Variables インスタンスの照合順序Instance Collation COLLATECATALOG_DEFAULT
Goto ラベルGoto Labels インスタンスの照合順序Instance Collation COLLATECATALOG_DEFAULT
カーソル名Cursor Names インスタンスの照合順序Instance Collation COLLATECATALOG_DEFAULT

前に説明した一時テーブルの例でわかるように、この照合順序の動作によって、多くの一時テーブルでは明示的に COLLATE 句を使用する必要がなくなります。If we temp table example previously described, we can see that this collation behavior eliminates the need for an explicit COLLATE clause in most temp table uses. 包含データベースでは、データベースとインスタンスの照合順序が異なる場合でも、このコードはエラーにならずに実行されます。In a contained database, this code now runs without error, even if the database and instance collations differ:

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_txtT2_txt の両方が、包含データベースのデータベース照合順序で照合されるためです。This works because both T1_txt and T2_txt are collated in the database collation of the contained database.

包含コンテキストと非包含コンテキスト間の移動Crossing Between Contained and Uncontained Contexts

包含データベース内のセッションが包含されたままである限り、そのセッションは接続されているデータベース内で維持されます。As long as a session in a contained database remains contained, it must remain within the database to which it connected. この場合の動作は非常に単純です。In this case the behavior is very straightforward. しかし、セッションが包含コンテキストと非包含コンテキスト間にまたがると、2 つの規則のセットに対応する必要があるので、動作はより複雑になります。But if a session crosses between contained and non-contained contexts, the behavior becomes more complex, since the two sets of rules must be bridged. この問題は、部分的包含データベースで発生する場合があります。ユーザーが USE で別のデータベースを使用できるためです。This can happen in a partially-contained database, since a user may USE to another database. この場合、照合順序の規則の違いは、以下の原則に従って処理されます。In this case, the difference in collation rules is handled by the following principle.

  • バッチの照合順序の動作は、バッチを開始したデータベースによって決定されます。The collation behavior for a batch is determined by the database in which the batch begins.

この決定は、最初の USEも含めて、どのコマンドの実行よりも前に行われることに注意してください。Note that this decision is made before any commands are issued, including an initial USE. つまり、バッチが包含データベース内で開始されると、最初のコマンドが非包含データベースに対する USE であっても、バッチで使用されるのは包含の照合順序の動作のままです。That is, if a batch begins in a contained database, but the first command is a USE to a non-contained database, the contained collation behavior will still be used for the batch. このため、たとえば変数への参照が、次のような複数の結果を持つ可能性があります。Given this, a reference to a variable, for example, may have multiple possible outcomes:

  • 参照がただ 1 つの一致を見つけられる。The reference may find exactly one match. この場合、参照はエラーにならずに動作します。In this case, the reference will work without error.

  • 参照が現在の照合順序では一致を見つけられないが、前の照合順序では 1 つ見つかった。The reference may not find a match in the current collation where there was one before. この場合は、変数が作成されたことが明らかであっても、変数が存在しないというエラーが生成されます。This will raise an error indicating that the variable does not exist, even though it was apparently created.

  • 参照が、本来は区別可能であった複数の一致を見つけられる。The reference may find multiple matches that were originally distinct. この場合も、エラーが生成されます。This will also raise an error.

この問題を、いくつかの例で説明します。We'll illustrate this with a few examples. これらの例では、 MyCDB という名前の部分的包含データベースがあり、そのデータベースの照合順序が既定の照合順序の Latin1_General_100_CI_AS_WS_KS_SCに設定されているものとします。For these we assume there is a partially-contained database named MyCDB with its database collation set to the default collation, Latin1_General_100_CI_AS_WS_KS_SC. また、インスタンスの照合順序は Latin1_General_100_CS_AS_WS_KS_SCであるとします。We assume that the instance collation is Latin1_General_100_CS_AS_WS_KS_SC. 2 つの照合順序の違いは、大文字と小文字を区別するかどうかだけです。The two collations differ only in case sensitivity.

例 1Example 1

次の例は、参照がただ 1 つの一致を見つける場合を示しています。The following example illustrates the case where the reference finds exactly one match.

USE MyCDB;  
GO  
  
CREATE TABLE #a(x int);  
INSERT INTO #a VALUES(1);  
GO  
  
USE master;  
GO  
  
SELECT * FROM #a;  
GO  
  
Results:  
  

以下に結果セットを示します。Here is the result set.

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

この例では、識別された #a が、大文字と小文字を区別しないカタログ照合順序と、大文字と小文字を区別するインスタンス照合順序の両方でバインドされ、コードは正常に動作します。In this case, the identified #a binds in both the case-insensitive catalog collation and the case-sensitive instance collation, and the code works.

例 2Example 2

次の例は、参照が現在の照合順序では一致を見つけられず、前の照合順序では 1 つ見つけた場合を示しています。The following example illustrates the case where the reference does not find a match in the current collation where there was one before.

USE MyCDB;  
GO  
  
CREATE TABLE #a(x int);  
INSERT INTO #A VALUES(1);  
GO  

ここでは、大文字と小文字を区別しない既定の照合順序で #A が #a にバインドされ、挿入を正常に実行できます。Here, the #A binds to #a in the case-insensitive default collation, and the insert works,

以下に結果セットを示します。Here is the result set.

(1 row(s) affected)  

しかし、スクリプトを続行すると ...But if we continue the script...

USE master;  
GO  
  
SELECT * FROM #A;  
GO  

大文字と小文字を区別するインスタンスの照合順序で #A にバインドしようとして、エラーになります。We get an error trying to bind to #A in the case-sensitive instance collation;

以下に結果セットを示します。Here is the result set.

メッセージ 208、レベル 16、状態 0、行 2Msg 208, Level 16, State 0, Line 2

オブジェクト名 '#A' が無効です。Invalid object name '#A'.

例 3Example 3

次の例は、参照が本来は区別可能であった複数の一致を見つける場合を示しています。The following example illustrates the case where the reference finds multiple matches that were originally distinct. まず、 tempdb 内 (インスタンスと同様に、大文字と小文字を区別する照合順序) から、以下のステートメントを実行します。First, we start in tempdb (which has the same case-sensitive collation as our instance) and execute the following statements.

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  

この照合順序では、テーブルが区別可能であるため、問題なく動作します。This succeeds, since the tables are distinct in this collation:

以下に結果セットを示します。Here is the result set.

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

しかし、包含データベース内に移動すると、これらのテーブルへのバインドができなくなります。If we move into our contained database, however, we find that we can no longer bind to these tables.

USE MyCDB;  
GO  
SELECT * FROM #a;  
GO  

以下に結果セットを示します。Here is the result set.

メッセージ 12800、レベル 16、状態 1、行 2Msg 12800, Level 16, State 1, Line 2

一時テーブル名 '#a' への参照があいまいで、解決できません。The reference to temp table name '#a' is ambiguous and cannot be resolved. 候補となるのは '#a' と '#A' です。Possible candidates are '#a' and '#A'.

まとめConclusion

包含データベースの照合順序の動作は、非包含データベースの場合と微妙に異なります。The collation behavior of contained databases differs subtly from that in non-contained databases. この動作は、インスタンスに対する独立性と簡易性を提供し、一般的には有益です。This behavior is generally beneficial, providing instance-independence and simplicity. ただし、場合によっては、特にセッションが包含データベースと非包含データベースの両方にアクセスする場合には、問題が発生することもあります。Some users may have issues, particularly when a session accesses both contained and non-contained databases.

参照See Also

包含データベースContained Databases