Maximum size of a varchar max variable

Question

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

User · Answer

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

User · Answer

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 isn t supported for varchar max  variables   Addition  I ve also tested the behaviour with concatenating nvarchar max    nvarchar max  and nvarchar max    varchar max   Both of these allow the 2GB limit to be exceeded  Trying to then store the results of this in a table then fails however with the error message Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes  again  The script for that is below  may take a long time to run    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

[sql-server] Maximum size of a varchar(max) variable

Examples related to sql-server

Examples related to tsql