SQL Server 2005 以降では BLOB を格納する際のデータ型は text / ntext / image ではなく、varchar(max) / nvarchar(max) / varbinary(max) が推奨となっています。
型の変更だけでなく、[large value types out of row] というテーブルオプションも追加されています。
今回はこのテーブルオプションについて見ていきたいと思います。
■デフォルトの状態の BLOB データの格納
まずは、デフォルトの状態 (large value types out of row が OFF) で BLOB のデータがどのように格納されるかを見ていきたいと思います。
使用するテーブルはこちら。
|
CREATE TABLE [dbo].[Table_1]( |
int の主キーと BLOB の列を持つシンプルな構造のテーブルです。
このテーブルに以下のクエリでデータを INSERT します。
| INSERT Table_1 (Col2) VALUES(REPLICATE(N’A’, 10)) |
これで準備は完了です。
ページ情報を確認してみたいと思います。
|
DBCC IND(N’TEST’, N’dbo.Table_1′, 1) |
432 ページの情報を確認していますが、このページ番号は DBCC IND を実行し、取得したものを設定します。
取得したページの情報がこちらになります。
|
Slot 0 Offset 0x60 Length 25 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 0000000000000000: 30000800 01000000 02004c01 00190041 41414141 †0………L….AAAAA Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 = 1 Col2 = [BLOB Inline Data] Slot 0 Column 2 Offset 0xf Length 10 Length (physical) 10 00000000301BF920: 41414141 41414141 4141††††††††††††††††††††††††AAAAAAAAAA Slot 0 Offset 0x0 Length 0 Length (physical) 0 |
デフォルトの状態では、 [BLOB Inline Data] (行内レコード) として、データ行に BLOB の列が直接格納されていることが確認できます。
これは DBCC IND の結果からも確認することができます。
今回使用しているテーブルは IAM (PageType = 10) と データページ (PageType = 1) で構成されていることが実行結果からわかります。
# PagePID が 432 から変わってしまっているのは、データを消してから画面キャプチャを取得したためです…。 
1 ページのサイズは 8KB と決まっていますので、それを超えるデータに関しては、行外データとして LOB ストレージ領域に格納がされます。
# オプションを設定しないと、8,000 バイトを超えるデータを格納できないというわけではありません。
8KB を超える文字列を INSERT 下場合の結果がこちらになります。
データページ以外に LOB データ (PageType = 3) のページが追加されていることが確認できますね。 
この場合のページの情報は以下のようになります。
|
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 = 1 Col2 = [BLOB Inline Root] Slot 0 Column 2 Offset 0xf Length 36 Length (physical) 36 Level = 0 Unused = 255 UpdateSeq = 1 Size = 8040 RowId = (1:473:0) Size = 8164 RowId = (1:472:0) Slot 0 Offset 0x0 Length 0 Length (physical) 0 |
行内データのときとは異なり、[BLOB Inline Root] という種別になっています。
ページ内に LOB のデータがおさまらない場合は、ページのポインタが列に格納されます
■large value types out of row を有効化
それでは、[large value types out of row] を有効にしてみます
。
| sp_tableoption ‘dbo.Table_1’, ‘large value types out of row’, ‘ON’ |
この状態で先ほどと同じ INSERT のクエリを実行してデータを挿入し、ページの情報を取得してみます。 
先ほどと異なり、ページ内に収まるデータでも [LOB data] のページが存在していることが確認できます。
それでは、データページである [288] ページの情報を確認してみます。
|
Slot 0 Offset 0x60 Length 31 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 0000000000000000: 30000800 01000000 0200b401 001f8000 00112700 †0………´…….’. Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 = 1 Col2 = [Textpointer] Slot 0 Column 2 Offset 0xf Length 16 Length (physical) 16 TextTimeStamp = 655425536 RowId = (1:280:0) Slot 0 Offset 0x0 Length 0 Length (physical) 0 |
[Textpointer] として、LOB へのポインターが格納されていることが確認できますね。
[large value types out of row] を有効にすると、LOB は行内データではなく行外に格納されるようになりますのでこのようなデータ格納となります。
行内にデータがあるばあいは、対象の行だけを検索すれば必要な結果を返すことができますので、検索時の取得コストは少なくて済みますが、データの密度としては低いものになります。
行外に出した場合は、LOB は行内には含まれませんので、1 ページ内に複数のレコードが格納できるようになりますが、LOB のデータを取得しようとした場合は必ずポインタを経由して他のページを読む必要がありますので検索時の取得コストが高くなります。(最低 2 ページ読む必要がありますので)
使い分けはケースバイケースになるとは思うのですが、LOB についてぼんやりと考えていたらこのような設定があったので少しまとめてみました。
[…] value types out of row の有無による検索への影響 以前、large value types out of row と Textpointer と BLOB Inline Data という投稿で少し書いたのですが、このオプションは8,000 バイト以下の […]
large value types out of row の設定による検索への影響 « SE の雑記
22 6月 11 at 08:30