SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Contained Databases を使用した場合の一時テーブルの照合順序について

leave a comment

Contained Databases を使用したログインを意識しないデータベースの移動 の続きになります。

上記の投稿では Contained Databases を使用して、データベースのユーザーにログインとしての情報を含め、データベースの移動時のログイン作成にどのように影響するかを書きました。

Contained Databases を使用することでデータベースの照合順序を一時テーブルのデフォルトの照合順序として使用できるようになります。
# 通常、tempdb の照合順序はインストール時に設定した照合順序となります。

今回はこの、Contained Database Collations について軽くまとめてみたいと思います。

■Contained Databases を使用した場合の一時テーブルの照合順序


まずは、Contained Databases を使用しない場合の一時テーブルの照合順序の動作を確認してみたいと思います。

今回のインスタンスの tempdb ですが、[Japanese_XJIS_100_CI_AS] となっています。
大文字小文字は区別しない設定となっていますので比較時に [A] と [a] は同一になります。
image

照合順序を [Japanese_XJIS_100_BIN2] に設定して、Contained Databases は無効の状態にしています。

image

このデータベースはバイナリ照合順序ですので検索時に文字の大文字小文字等が区別される設定となります。
# [A] と [a] は別になります。

このデータベースで以下のクエリを実行してみます。

USE [TEST2]
CREATE TABLE #tmp(Col1 char(1))
INSERT INTO #tmp VALUES (‘A’)
SELECT * FROM #tmp WHERE Col1 = ‘a’
DROP TABLE #tmp

実行しているデータベースの照合順序では大文字と小文字を区別する設定となっていますが、一時テーブルの照合順序は tempdb のものが使用されるため大文字と小文字は区別されずに検索がされます。
image

それでは、同様の照合順序で Contained Databases が有効になっているデータベースに対して同様のクエリを実行してみます。
image

USE [TEST]
CREATE TABLE #tmp(Col1 char(1))
INSERT INTO #tmp VALUES (‘A’)
SELECT * FROM #tmp WHERE Col1 = ‘a’
DROP TABLE #tmp

Contained Databases が有効になっている場合には選択しているデータベースの照合順序が使用されるため、[A] と [a] が区別されます。
そのため結果が変わってきます。

image

 

このあたりの設定がまとめられているのが本投稿の最初に記載したリンクになります。
以下は、リンク先から表をコピーしたものになります。
image

Contained Databases が有効になっている一時データは [Tempdb Collation] ではなく [DATABASE_DEFAULT] が使用されるようになります。
# テーブル変数はどちらの場合もデータベースの照合順序になるようですが。

そのため、有効になっている場合は作成した一時テーブルでデータベースの照合順序である [Japanese_XJIS_100_BIN2] が使用されることになります。

そのため、tempdb では大文字小文字を区別しない設定となっていても、バイナリ照合順序で比較が行われるため、大文字小文字の区別がされる状態となります。

このような動作をするようになることで、Contained Databases を有効にした場合にはインストール時の照合順序に依存しない比較ができるようになってきます。
他のインスタンスにデータベースを移動した際の比較順への影響が抑えられるようになるかと。
# どこまで想定通りの動作をするかは検証する必要がありますが。

Denali になると、Contained Databases を使用することでサーバーに設定に依存せずにデータベースの設定で一定の環境が作れるようになってくるようですね。

HADR (Always On) に関しても、導入を軽くまとめて以降追加の情報を書いていなかったのでぼちぼちまとめていきたいと思います。

Written by masayuki.ozawa

7月 7th, 2011 at 8:34 pm

Posted in SQL Server

Tagged with ,

Leave a Reply

*