Maximum size of a varchar(max) variable

Asked 2011-09-30T13:52:54.643

106

At any time in the past, if someone had asked me the maximum size for a varchar(max), I'd have said 2GB, or looked up a more exact figure (2^31-1, or 2147483647).

However, in some recent testing, I discovered that varchar(max) variables can apparently exceed this size:

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

Results:

(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)

So, given that I now know that a variable can exceed the 2GB barrier - does anyone know what the actual limit is for a varchar(max) variable?

(Above test completed on SQL Server 2008 (not R2). I'd be interested to know whether it applies to other versions)

sql-server t-sql

Edit Report

2 Answers

As far as I can tell there is no upper limit in 2008.

In SQL Server 2005 the code in your question fails on the assignment to the @GGMMsg variable with

Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes.

the code below fails with

REPLICATE: The length of the result exceeds the length limit (2GB) of the target large type.

However it appears these limitations have quietly been lifted. On 2008

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

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

SELECT LEN(@y)

Returns

8589767761

I ran this on my 32 bit desktop machine so this 8GB string is way in excess of addressable memory

Running

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

Returned

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

so I presume this all just gets stored in LOB pages in tempdb with no validation on length. The page count growth was all associated with the SET @y = REPLICATE(@y,92681); statement. The initial variable assignment to @y and the LEN calculation did not increase this.

The reason for mentioning this is because the page count is hugely more than I was expecting. Assuming an 8KB page then this works out at 16.36 GB which is obviously more or less double what would seem to be necessary. I speculate that this is likely due to the inefficiency of the string concatenation operation needing to copy the entire huge string and append a chunk on to the end rather than being able to add to the end of the existing string. Unfortunately at the moment the .WRITE method answered 2011-09-30T15:58:46.317

EDIT: After further investigation, my original assumption that this was an anomaly (bug?) of the declare @var datatype = value syntax is incorrect.

I modified your script for 2005 since that syntax is not supported, then tried the modified version on 2008. In 2005, I get the Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. error message. In 2008, the modified script is still successful.

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)

answered 2011-09-30T14:12:52.217

Maximum size of a varchar(max) variable

2 Answers

Your Answer