行内データ

更新 : 2006 年 12 月 12 日

SQL Server 2005 では、小から中程度までの大きさの値を持つ型 (varchar(max)、nvarchar(max)、varbinary(max)、および xml) と LOB (ラージ オブジェクト) データ型 (text、ntext、および image) を 1 行のデータ行に混在させることができます。この動作を制御するには、sp_tableoption システム ストアド プロシージャの large value types out of row オプション (大きな値を含む型に使用) および text in row オプション (ラージ オブジェクト型に使用) を使用します。この 2 つのオプションの使用が適しているのは、今挙げたいずれかのデータ型の値が 1 つのまとまりとして読み書きされるようなテーブルで、そのテーブルを参照するステートメントのほとんどがこのようなデータを参照しているような場合です。用途または負荷の特性によっては、行内データの格納が適さない場合があります。

重要 :
text in row オプションは今後のバージョンの SQL Server で廃止される予定です。新しい開発作業では、このオプションを使用しないでください。また、現在 text in row を使用しているアプリケーションがあれば、変更を計画してください。大きなデータは varchar(max)、nvarchar(max)、varbinary(max) のいずれかの型で格納することをお勧めします。これらのデータ型の行内および行外での動作を制御するには、large value types out of row オプションを使用します。

text in row オプションを ON または行内での制限値に設定している場合を除き、text 型、ntext 型、または image 型の文字列はキャラクタまたはバイナリの大きな文字列 (最大 2 GB) としてデータ行の外部に格納されます。データ行には、内部的に使用するポインタから構成されるツリーのルート ノードを指す 16 バイトのテキスト ポインタのみが格納されます。内部的に使用するポインタには、文字列の断片が保存されたページがマップされています。text 型、ntext 型、または image 型の文字列の格納については、「text 型データと image 型データの使用」を参照してください。

LOB データ型の列を含むテーブルには text in row オプションを設定できます。また、24 ～ 7,000 バイトの範囲内で、text in row オプションの制限を指定できます。

同様に、large value types out of row オプションが ON に設定されている場合を除き、varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、および xml 型の列は、可能であればデータ行の内部に格納されます。その場合、指定した値の格納がデータベース エンジンによって試みられ、可能であれば値が格納され、不可能であれば値は行外に押し出されます。large value types out of row が ON に設定されている場合、値は行外に保存され、レコードには 16 バイトのテキスト ポインタのみが格納されます。

メモ :
large value types out of row が OFF の場合、大きな値を含むデータ型が行内に格納できる最大データは 8,000 バイトです。text in row オプションとは異なり、テーブルの列に対して行内の制限値を指定できません。

構成上、大きな値を含む型またはラージ オブジェクト データ型を直接データ行に格納できるテーブルでは、次のいずれかの条件を満たす場合に、実際の列値が行内に格納されます。

• 文字列の長さが、text 型、ntext 型、および image 型の列に対して指定されている制限よりも短い。
• 文字列を格納するための十分な空き領域がデータ行にある。

大きな値を含む型またはラージ オブジェクト データ型の列値がデータ行に格納されているとき、データベース エンジンは別のページまたはページ セットにアクセスしなくても、キャラクタ文字列またはバイナリ文字列の読み取りや書き込みを行うことができます。これにより、行内の文字列の読み取りと書き込みが、varchar 型、nvarchar 型、または varbinary 型のサイズの制限された文字列の読み取りや書き込みと同程度に高速になります。同様に、値が行外に格納されている場合、データベース エンジンが別のページの読み取りまたは書き込みを行うことになります。

ラージ オブジェクト データ型は、text in row オプションの制限または行で使用できる領域よりも文字列が長い場合、ポインタのセットを行に格納します。行に格納されない場合、そのセットはポインタ ツリーのルート ノードに格納されます。次のいずれかの条件を満たす場合、ポインタは行に格納されます。

• ポインタの格納に必要な領域が、text in row オプションで指定されている制限より小さい。
• ポインタを格納するための十分な空き領域がデータ行にある。

ポインタをルート ノードから行自体に移動すると、データベース エンジンでルート ノードを使用する必要がなくなります。これにより、文字列の読み取り中または書き込み中にページにアクセスする必要がなくなります。その結果、パフォーマンスが向上します。

ルート ノードを使用する場合、ポインタは LOB 型のページに文字列の断片の 1 つとして格納され、内部ポインタを 5 つまで保持できます。データベース エンジンで行内の文字列 1 つについて 5 つのポインタを格納するには、その行に 72 バイトの領域が必要です。text in row オプションが ON であるか、large value types out of row オプションが OFF である場合に、ポインタを格納するだけの十分な領域が行にない場合、ポインタを格納するための領域として 8 KB をデータベース エンジンが割り当てる必要性が生じる場合があります。値のデータ長が 40,200 バイトを超えると 5 つの行内ポインタでは足りなくなり、メインの行には 24 バイトのみが格納され、追加のデータ ページが LOB の格納領域に割り当てられます。

大きな値を含む文字列は、可変長の文字列と同様に行に格納されます。データベース エンジンは列をサイズの大きい順に並べ替え、残った列をデータ ページ (8 KB) に格納できるまで値を行外に押し出していきます。

large value types out of row オプションの有効化と無効化

テーブルの large value types out of row オプションを有効にするには sp_tableoption を次の方法で使用します。

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

OFF を指定した場合、varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、および xml 型の列の行内での制限は 8,000 バイトです。行内には 16 バイトのルート ポインタのみが格納され、値は LOB の格納領域に格納されます。テーブルに大きな値を含む型の列があっても、その列を参照するステートメントがほとんどない場合は、このオプションを ON にしておくことをお勧めします。大きな値を含む型の列を行外に格納することで 1 ページに格納できる行数が増えるので、テーブルをスキャンするために必要な I/O 操作の数が減ります。

このオプションの値を OFF に設定すると多くの文字列が行自体に格納されることになり、1 ページに格納できるデータ行の数が減少します。テーブルを参照するステートメントのうち、ごく一部しか varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、および xml 型の列にアクセスしない場合、1 ページあたりの行数が減少し、クエリを処理するために読み取るページ数が増加することがあります。また、1 ページあたりの行数が減少すると、使用可能なインデックスを検出できなかった場合に、オプティマイザによるスキャンを必要とするページ数が増加する可能性があります。

sp_tableoption は、large value types out of row オプションを無効にする場合にも使用できます。

sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

large value types out of row オプションの値を変更しても、varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、および xml 型の既存の値が即座に変換されるわけではありません。文字列の格納場所は、これ以降の更新時に変更されます。テーブルに挿入される行は、テーブル オプションに従って格納されます。

特定のテーブルの large value types out of row オプションの値を調べるには、sys.tables カタログ ビューの large_value_types_out_of_row 列に対してクエリを実行します。この列は、テーブルの large value types out of row が有効ではない場合は 0、大きい値の型が行外に格納されている場合は 1 になります。

text in row オプションの有効化と無効化

テーブルの text in row オプションを有効にするには sp_tableoption を次の方法で使用します。

sp_tableoption N'MyTable', 'text in row', 'ON'

必要に応じて、text 型、ntext 型、および image 型の文字列をデータ行に格納できる最大長を、24 ～ 7,000 バイトの範囲内で指定することもできます。

sp_tableoption N'MyTable', 'text in row', '1000'

制限値を指定する代わりに ON を指定すると、制限値は既定の 256 バイトに設定されます。この既定値を使用すると、text in row オプションによるパフォーマンス上の利点のほとんどを実現することができます。一般的にこの値は、72 未満に設定したり、逆に大きすぎる値に設定しないようにする必要があります。この推奨事項はテーブルの text 型、ntext 型、および image 型の列を参照するステートメントがほとんどない場合、または text 型、ntext 型、および image 型の列がテーブル内に複数ある場合に特に当てはまります。

text in row オプションの制限に大きい値を設定することで、行自体に格納する文字列を増加した場合、1 ページあたりのデータ行の数が大幅に減少することがあります。テーブルを参照するほとんどのステートメントが text 型、ntext 型、または image 型の列にアクセスしない場合、1 ページあたりの行数が減少し、クエリを処理するために読み取るページ数が増加することがあります。また、1 ページあたりの行数が減少すると、インデックス サイズが大きくなり、使用可能なインデックスを検出できなかった場合に、オプティマイザによるスキャンを必要とするページ数が増加する可能性があります。text in row オプションの制限の既定値は 256 です。この値は、短い文字列およびルート テキスト ポインタを行に格納するには十分でありながら、1 ページあたりの行数が減少してパフォーマンスに影響するほど大きすぎることもありません。

table データ型の変数、または table 型の値を返すユーザー定義関数が返すテーブルについては、text in row オプションが自動的に 256 に設定されます。この設定は変更できません。

sp_tableoption を使用してオプションの値を OFF または 0 に指定することで、オプションを無効にすることもできます。

sp_tableoption N'MyTable', 'text in row', 'OFF'

特定のテーブルの text in row オプションの値を調べるには、sys.tables カタログ ビューの text_in_row_limit 列に対するクエリを実行します。そのテーブルで text in row が有効になっていない場合は 0 が、行内の制限が設定されている場合は 0 より大きい値が返されます。

text in row オプションの効果

text in row オプションには、次の効果があります。

• text in row オプションを有効にすると、TEXTPTR、READTEXT、UPDATETEXT、または WRITETEXT ステートメントを使用して、テーブルに格納されている text 型、ntext 型、または image 型の値を部分的に読み取ったり、変更したりすることができます。SELECT ステートメントでは、text 型、ntext 型、または image 型の文字列全体を読み取ることができます。また、SUBSTRING 関数を使用すると、文字列を部分的に読み取ることができます。テーブルを参照するすべての INSERT ステートメントまたは UPDATE ステートメントでは、文字列全体を指定する必要があります。text 型、ntext 型、または image 型の文字列の一部のみを変更することはできません。
• text in row オプションを初めて有効にする場合、text 型、ntext 型、または image 型の既存の文字列が行内の文字列に即座に変換されるわけではありません。文字列が行内の文字列に変換されるのは、それ以降に更新される場合のみです。text in row オプションを有効にした後に text 型、ntext 型、または image 型の文字列を挿入するときは、行内の文字列として挿入されます。
• text in row オプションを無効にする操作はログに記録され、長時間かかることがあります。テーブルがロックされ、text 型、ntext 型、および image 型のすべての行内の文字列が text、ntext、および image 型の標準の文字列に変換されます。コマンドの実行時間および変更されるデータ量は、行内の文字列から標準の文字列への変換を必要とする text 型、ntext 型、および image 型の文字列の数によって決まります。
• text in row オプションを使用しても、text 型、ntext 型、および image 型のデータへのアクセスが高速化することを除き、SQL Native Client OLE DB プロバイダまたは SQL Native Client ODBC ドライバの動作に影響はありません。
• text in row オプションを有効にすると、dbreadtext、dbwritetext などの DB-Library テキスト関数およびイメージ関数がテーブルに対して使用できなくなります。

参照

概念

列へのデータ型の割り当て

その他の技術情報

テーブルのデザイン

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 12 月 12 日	新しい内容 : 「text in row オプションの有効化と無効化」で、特定のテーブルの large value types out of row オプションの値の調査と sys.tables カタログ ビューの large_value_types_out_of_row 列に対するクエリの実行に関する段落を追加しました。