Tamaño máximo de una variable varchar(max)

En cualquier momento del pasado, si alguien me hubiera preguntado el tamaño máximo para un varchar(max), habría dicho 2 GB, o buscado una figura más exacta (2^31-1, ó 2147483647).

Sin embargo, en algunas pruebas recientes, descubrí que varchar(max) las variables aparentemente pueden exceder este tamaño: 

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

Resultados: 

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

Así que, dado que ahora sé que una variable puede exceder la barrera de 2 GB, ¿alguien sabe cuál es el límite real para una varchar(max) variable?

(Prueba anterior completada en SQL Server 2008 (no R2). Me interesaría saber si se aplica a otras versiones)

Author: Damien_The_Unbeliever, 2011-09-30
Source

3 answers

Por lo que puedo decir no hay límite superior en 2008.

En SQL Server 2005 el código en su pregunta falla en la asignación a la variable @GGMMsg con

Tratando de crecer LOB más allá del tamaño máximo permitido de 2,147,483,647 byte.

El siguiente código falla con

REPLICAR: La longitud del resultado excede el límite de longitud (2 GB) de el tipo objetivo grande.

Sin embargo, parece que estas limitaciones tienen en silencio se levantó. En 2008

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

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

SELECT LEN(@y)

Devuelve 

8589767761

Corrí esto en mi máquina de escritorio de 32 bits por lo que esta cadena de 8 GB es mucho más que la memoria direccionable

Corriendo 

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

Devuelto 

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

Así que supongo que todo esto se almacena en LOB páginas en tempdb sin validación de longitud. El crecimiento del número de páginas se asoció con la instrucción SET @y = REPLICATE(@y,92681);. La asignación inicial de la variable a @y y el cálculo de LEN no aumentaron este.

La razón para mencionar esto es porque el número de páginas es mucho más de lo que esperaba. Suponiendo que una página de 8KB entonces esto funciona en 16.36 GB que es obviamente más o menos el doble de lo que parece ser necesario. Especulo que esto probablemente se deba a la ineficiencia de la operación de concatenación de cadenas que necesita copiar toda la cadena enorme y anexar un trozo al final en lugar de poder agregar al final de la cadena existente. Desafortunadamente en este momento el método .WRITE no está soportado para variables varchar(max).

Adición

También he probado el comportamiento con la concatenación nvarchar(max) + nvarchar(max) y nvarchar(max) + varchar(max). Ambos permiten superar el límite de 2GB. Tratando de almacenar los resultados de esto en una tabla entonces falla sin embargo con el mensaje de error Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. de nuevo. El script para eso se encuentra a continuación (puede tardar mucho tiempo en ejecutarse).

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
 66
Author: Martin Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-09-29 11:12:56

EDITAR : Después de más investigación, mi suposición original de que esto era una anomalía (bug?) de la sintaxis declare @var datatype = value es incorrecta.

Modificé su script para 2005 ya que la sintaxis no es compatible, luego probé la versión modificada en 2008. En 2005, recibo el mensaje de error Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.. En 2008, el script modificado sigue siendo exitoso.

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)
 9
Author: Joe Stefanelli,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-09-30 14:44:14

Puede almacenar hasta 4000 letras de texto en varchar en oracle 10g.

 0
Author: Arun Raaj,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-25 14:35:58