SQLServer2008での1レコードバイト数の上限を増やしたい

質問

こんにちは、いつも参考にさせていただいております。

通常1レコードのバイト数の上限は8060ですが、
それがSQLServer2008あたりから緩和されたと聞きました。
そこでマイクロソフトのTechNetを見ましたがよくわかりませんでした。
２つわからない点がありますのでご教授どうかお願いいたします。

◆疑問点(1)◆
↓で8060バイト上限が緩和できそうだと思った中での疑問です。

マイクロソフトのTechNet
「8 KB を超える場合の行オーバーフロー データ」
http://msdn.microsoft.com/ja-jp/library/ms186981.aspx

「これら（varchar 型、nvarchar 型、varbinary 型、sql_variant 型、またはCLR ユーザー定義型）の列の長さは、単独の場合は引き続き 8,000 バイトに制限されますが、組み合わせた場合は 8,060 バイトの制限を超えることができます。」

わからないのは、上記文章中にある「組み合わせた場合」というポイントです。
○varchar 型とnvarchar 型、varbinary 型とsql_variant 型というように違う種類を組み合わせなければ8060バイト制限が越えられないのでしょうか？

◆疑問点(2)◆
「8 KB を超える場合の行オーバーフロー データ」ページ内でntext型などについての注意書きがありました（下記）。
それについての疑問です。
「この制限は、varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、ntext 型、text 型、image 型、または xml 型の列には適用されません。」

○ntext型とvarchar 型等との組み合わせの場合、8060バイト制限は関係ないのでしょうか？
　（というよりntext型などは8060バイトレコード上限が関係ない？）

「text 型データと image 型データの使用」
http://msdn.microsoft.com/ja-jp/library/ms189574.aspx

どうかご教授・ヒントなどいただけないでしょうか。
よろしくお願いいたします。
補足など必要でしたらコメントください。

回答No.4

jamshid6 · ベストアンサー

#2です。
>おっしゃっていることをまとめてみたのですが↓であっていますでしょうか？
>・新設されたvarchar(max) 型などのLOBは1列ごとに16バイト、1行で最大500バイトまでのポインタを保持する
>・従来のままの型で1行で8Kを超える場合1行全体で24バイトのポインタを保持する

・新設されたvarchar(max) 型などのLOBは1列ごとに16バイトのポインタを保持するが、先頭部分をデータエリア内に保持するというオプションを指定すると、最大500バイトまでの部分は8Kのデータページに保持される
・従来のままの型で1行で8Kを超える場合、超えたとSQL Serverが判断した時点で超えた列について、１列につき24バイトのポインタを保持させ、超えた部分を行オーバフローページに押し出す

ということかと思います。
この辺は「Inside SQL Server 2005 ストレージエンジン編」という本にくわしく書かれていますよ。

お礼

jamshid6さん、ご回答ありがとう御座います。

> ・新設されたvarchar(max) 型などのLOBは1列ごとに16バイトのポインタを保持するが、先頭部分をデータエリア内に保持するというオプションを指定すると、最大500バイトまでの部分は8Kのデータページに保持される
> ・従来のままの型で1行で8Kを超える場合、超えたとSQL Serverが判断した時点で超えた列について、１列につき24バイトのポインタを保持させ、超えた部分を行オーバフローページに押し出す
そういう仕組みなんですね。
言葉を言い換えて頂いたのでよくわかりました。
本当にありがとうございます。

> この辺は「Inside SQL Server 2005 ストレージエンジン編」という本にくわしく書かれていますよ。
早速読んでみたいと思います。
とても勉強になりました。ありがとうございました。

回答No.3

kuroizell

私なりの解釈まとめ。

・溢れた分は、"他所"に保管します(その際、24バイト分の"他所"の場所情報を保存)
・varchar(max)等は最初から"他所"として、16バイト分の場所情報だけを保存します
・"他所"の場所情報込みで、1レコード8KBまでですよ。

ってトコでしょうか。

お礼

kuroizellさん、ご回答ありがとう御座います。

> ・溢れた分は、"他所"に保管します(その際、24バイト分の"他所"の場所情報を保存)
> ・varchar(max)等は最初から"他所"として、16バイト分の場所情報だけを保存します
> ・"他所"の場所情報込みで、1レコード8KBまでですよ。
整理していただきありがとうございます。
だいぶ混乱していたようです。
色々と勘違いばかりしていましたが、ようやくわかってきました。
とても助かりました。ありがとうございました。

回答No.2

jamshid6

SQL Server 2005以降の変更ですが、別にデータページのサイズが変わったわけではなく、従来と同じ8Kのままです。ただ、複数のフィールドの合計が8Kを超えた場合は行オーバーフローページにあふれたものを収録することができるようになったので、結果として8Kを超えるサイズのテーブルを作成できるようになったということです（オーバーフローした場合は24バイト分のポインタを8K内に保持します）。
「組み合わせた場合」というのは「データ型を組み合わせた場合」という意味ではありません。別にvarcharだけを組み合わせてもいいです。

SQL Server 2005で新設されたMAXがつく型は、そもそもLOB(ラージオブジェクト)ですので、8K内には16バイトのポインタしか保持しません。
（設定により500バイトまでは8K内に保持することができますが）
したがって、上記のサイズの対象外になります。

行オーバーフローにせよ、ラージオブジェクトにせよ、ポインタを保持して別のデータページにデータを保管しているわけですから、当然パフォーマンスには多少影響があることになります。また、ポインタとはいえ16ないし24バイトの領域を必要としますので、オーバーフローする列が多くなれば当然8Kのデータページ内の領域は食います。したがって、オーバーフローできる列数は無制限ではありません。

補足

jamshid6さん、ご回答ありがとう御座います。

>オーバーフローした場合は24バイト分のポインタを8K内に保持します
ここがどうなるかがよくわかっていませんでした。
そういう仕組みになっていたのですね、ご説明ありがとうございます。

>「組み合わせた場合」というのは「データ型を組み合わせた場合」という意味ではありません。別にvarcharだけを組み合わせてもいいです。
ありがとうございます。

> SQL Server 2005で新設されたMAXがつく型は、そもそもLOB(ラージオブジェクト)ですので、8K内には16バイトのポインタしか保持しません。
> （設定により500バイトまでは8K内に保持することができますが）
> したがって、上記のサイズの対象外になります。
なるほど、ありがとうございます。

とするとお時間が許すならもう１つヒントをいただけないでしょうか。
おっしゃっていることをまとめてみたのですが↓であっていますでしょうか？
・新設されたvarchar(max) 型などのLOBは1列ごとに16バイト、1行で最大500バイトまでのポインタを保持する
・従来のままの型で1行で8Kを超える場合1行全体で24バイトのポインタを保持する

どうかよろしくお願いいたします。

回答No.1

kuroizell

MS SQLserverは触ったことないので、よく分かりませんが
＞「組み合わせた場合」
MSのサイト見ましたが、「1レコードあたりのデータ量は8060バイト制限ですが、varchar等を含む場合は別で、1カラムあたりは相変わらず8000バイト制限だけど、1レコード合計ではもっといけるようになりましたよ。」という風な意味に読めました。
MSのサイトの意味不明さには毎度閉口します。

＞「この制限は、varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、ntext 型、text 型、image 型、または xml 型の列には適用されません。」

> varchar [ ( n | max ) ]
> max は、記憶領域のサイズが 2^31-1 バイトであることを示しています。
http://msdn.microsoft.com/ja-jp/library/ms176089.aspx

varchar(max)っていうのが新しく出来て、「8000バイト超えるならそっち使って下さい、varchar(8000)とかとは別ルールですよ(この制限(中略)適用されません)」という風な意味でしょうか。

補足

kuroizellさん、ご回答ありがとう御座います。

> MS SQLserverは触ったことないので、よく分かりませんが
> ＞「組み合わせた場合」
> MSのサイト見ましたが、「1レコードあたりのデータ量は8060バイト制限ですが、varchar等を含む場合は別で、1カラムあたりは相変わらず8000バイト制限だけど、1レコード合計ではもっといけるようになりましたよ。」という風な意味に読めました。
> MSのサイトの意味不明さには毎度閉口します。
たしかに、そう読むとあの文章もしっくりしてきました。
わかりやすく説明していただきありがとうございます。

> ＞「この制限は、varchar(max) 型、nvarchar(max) 型、varbinary(max) 型、ntext 型、text 型、image 型、または xml 型の列には適用されません。」
> > varchar [ ( n | max ) ]
> > max は、記憶領域のサイズが 2^31-1 バイトであることを示しています。
> ?http://msdn.microsoft.com/ja-jp/library/ms176089.aspx?
> varchar(max)っていうのが新しく出来て、「8000バイト超えるならそっち使って下さい、varchar(8000)とかとは別ルールですよ(この制限(中略)適用されません)」という風な意味でしょうか。
なるほどです。リンクもありがとうございます。
内容をそのまま、まとめると
・varchar(max)は1カラムで8000バイト超えるときに使う
・varchar(max)が使われる行のバイト数上限は、varchar(max)バイト数を含めない8060バイトとなる

というような感じでしょうか。
もしお時間があれば上記の内容について、ご指摘などいただけるとありがたいです。
私もSQLServerはほとんど触っていないので、文面からのヒントだけでも非常に助かりました。