Maximum size of a varchar(max) variable

by

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

More Related Contents:

  1. Turning a Comma Separated string into individual rows
  2. Comma separated results in SQL
  3. T-SQL stored procedure that accepts multiple Id values
  4. Understanding PIVOT function in T-SQL
  5. How do I escape a percentage sign in T-SQL?
  6. Using RegEx in SQL Server
  7. Fuzzy matching using T-SQL
  8. How do you count the number of occurrences of a certain substring in a SQL varchar?
  9. SQL Server: Isolation level leaks across pooled connections
  10. How do I use the CONCAT function in SQL Server 2008 R2?
  11. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  12. How do I exclude Weekend days in a SQL Server query?
  13. EXEC sp_executesql with multiple parameters
  14. Preserving ORDER BY in SELECT INTO
  15. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  16. SQL Server variable columns name?
  17. Can you call a webservice from TSQL code?
  18. How to add custom attributes to SQL connection string?
  19. Multiple Column Pivot in T-SQL
  20. Joining multiple tables returns NULL value
  21. Which join syntax is better?
  22. Compute MD5 hash of a UTF8 string
  23. LPAD in SQL Server 2008
  24. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  25. How do I remove non-breaking spaces from a column in SQL server?
  26. FOR JSON PATH results in SSMS truncated to 2033 characters
  27. How to set value to variable using ‘execute’ in t-sql?
  28. Update SQL with consecutive numbering
  29. T-SQL for finding Redundant Indexes
  30. Count number of records returned by group by

Leave a Comment