varchar(max) 변수의 최대 크기

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바이트를 초과하여 확장하려고 합니다.

아래 코드는 다음과 같이 실패합니다.

복제: 결과의 길이가 대상 대형 유형의 길이 제한(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.36GB로 작동하며, 이는 분명히 필요한 것의 두 배입니다.이것은 기존 문자열의 끝에 추가할 수 있는 것이 아니라 전체 거대 문자열을 복사하고 끝에 청크를 추가해야 하는 문자열 연결 작업의 비효율성 때문인 것으로 추측됩니다.불행하게도 그 순간에..WRITEvarchar(max) 변수에 대해 메서드가 지원되지 않습니다.

Paul White는 여기서 위의 가정을 확인하고 <= 512KB이고 그보다 큰 값에 대해 tempdb 기반 백업 체계로 변경될 경우 변수가 완전히 메모리에 저장된다는 정보도 제공합니다.

추가

저는 또한 연결된 행동을 테스트했습니다.nvarchar(max) + nvarchar(max)그리고.nvarchar(max) + varchar(max)이 두 가지 모두 2GB 제한을 초과할 수 있습니다. 이 에 저장하려고 하고 오류 " " " " " 그 다 이 결 과 를 실 고 메 오 가 지 시 표 됩 시 니 다 류 런 하 패 하 고 면 음 려 저 하 테 에 장 블 이 ▁trying ▁however ▁message ▁error 니 ▁fails ▁store ▁the ▁with ▁in 됩 그 ▁then ▁of ▁the 다 표런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

편집: 추가 조사 후, 이것이 비정상적인 (버그?) 것이라고 생각했던 나의 원래 추측.declare @var datatype = value구문이 잘못되었습니다.

해당 구문이 지원되지 않기 때문에 2005년용 스크립트를 수정한 후 2008년에 수정된 버전을 사용해 보았습니다.2005년에, 나는 그것을 받았습니다.Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.오류 메시지입니다.2008년에도 수정된 스크립트는 여전히 성공적입니다.

declare @KMsg varchar(max); set @KMsg = REPLICATE('a',1024);
declare @MMsg varchar(max); set @MMsg = REPLICATE(@KMsg,1024);
declare @GMsg varchar(max); set @GMsg = REPLICATE(@MMsg,1024);
declare @GGMMsg varchar(max); set @GGMMsg = @GMsg + @GMsg + @MMsg;
select LEN(@GGMMsg)

언급URL : https://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable