[解決済み] varchar(max) 変数の最大サイズ

質問

過去において、もし誰かが私に varchar(max) の最大サイズを尋ねられたら、私は 2GB と答えたでしょうし、より正確な 図 (2^31-1、または 2147483647) と言ったかもしれません。

しかし、最近のテストで、私は以下のことを発見しました。 varchar(max) 変数がこのサイズを超えることがあることを発見しました。

create table T (
    Val1 varchar(max) not null
)
go
declare @KMsg varchar(max) = REPLICATE('a',1024);
declare @MMsg varchar(max) = REPLICATE(@KMsg,1024);
declare @GMsg varchar(max) = REPLICATE(@MMsg,1024);
declare @GGMMsg varchar(max) = @GMsg + @GMsg + @MMsg;
select LEN(@GGMMsg)
insert into T(Val1) select @GGMMsg
select LEN(Val1) from T

結果

(no column name)
2148532224
(1 row(s) affected)
Msg 7119, Level 16, State 1, Line 6
Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.
The statement has been terminated.

(no column name)
(0 row(s) affected)

ということは、今、私が知っているのは 変数 が 2GB の壁を越えることができるということがわかったわけですが、実際に varchar(max) 変数に対する実際の制限値を知っている人はいますか？

(上記のテストは SQL Server 2008 (R2 ではありません) で完了しました。他のバージョンに適用されるかどうか知りたいです)

どのように解決するのですか？

私の知る限りでは、2008年には上限がありません。

SQL Server 2005では、ご質問のコードは、割り当てに失敗します。 @GGMMsg 変数への代入で失敗します。

LOB を最大許容サイズである 2,147,483,647 バイトを超えて大きくしようとしています。 バイトを超えました。

以下のコードは、以下のように失敗します。

REPLICATE: 結果の長さが対象のラージタイプの長さ制限(2GB)を超えています。 を超えています。

しかし、これらの制限は静かに解除されたようです。2008 年に

DECLARE @y VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),92681); 

SET @y = REPLICATE(@y,92681);

SELECT LEN(@y) 

戻り値

8589767761

私はこれを32ビットのデスクトップマシンで実行したので、この8GBの文字列はアドレス可能なメモリをはるかに超えています。

実行中

select internal_objects_alloc_page_count
from sys.dm_db_task_space_usage
WHERE session_id = @@spid

戻る

internal_objects_alloc_page_co 
------------------------------ 
2144456    

に格納されるだけだと思います。 LOB のページに格納されます。 tempdb で、長さの検証は行われませんでした。ページ数の増加は、すべて SET @y = REPLICATE(@y,92681); ステートメントに関連していました。最初の変数への代入は @y と LEN の計算では増加しなかった。

これを言及する理由は、ページ数が私が予想していたよりも非常に多いからです。8KB ページを仮定すると、これは 16.36 GB になり、明らかに必要であると思われる量の 2 倍以上です。これは、文字列の連結操作が、既存の文字列の末尾に追加するのではなく、巨大な文字列全体をコピーして末尾にチャンクを追加しなければならないという非効率性によるものだろうと推測しています。残念ながら現時点では .WRITE メソッド はサポートされていません。 をサポートしていません。

追加

を連結した場合の挙動もテストしてみました。 nvarchar(max) + nvarchar(max) と nvarchar(max) + varchar(max) . これらは両方とも2GBの制限を超えることを許可しています。この結果をテーブルに保存しようとすると、次のようなエラーメッセージが表示されて失敗します。 Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. というエラーメッセージが表示されます。そのためのスクリプトを以下に示します（実行には長い時間がかかるかもしれません）。

DECLARE @y1 VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),2147483647); 
SET @y1 = @y1 + @y1;
SELECT LEN(@y1), DATALENGTH(@y1)  /*4294967294, 4294967292*/


DECLARE @y2 NVARCHAR(MAX) = REPLICATE(CAST('X' AS NVARCHAR(MAX)),1073741823); 
SET @y2 = @y2 + @y2;
SELECT LEN(@y2), DATALENGTH(@y2)  /*2147483646, 4294967292*/


DECLARE @y3 NVARCHAR(MAX) = @y2 + @y1
SELECT LEN(@y3), DATALENGTH(@y3)   /*6442450940, 12884901880*/

/*This attempt fails*/
SELECT @y1 y1, @y2 y2, @y3 y3
INTO Test