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)
This question is related to
sql-server
tsql
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)
Source: Stackoverflow.com