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

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

Sin embargo, en algunas pruebas recientes, descubrí que las variables varchar(max) 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) 

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


(Por encima de la prueba completada en SQL Server 2008 (no R2). Me interesaría saber si se aplica a otras versiones)

Por lo que puedo decir, no hay un 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

Intentando boost el LOB más allá del tamaño máximo permitido de 2,147,483,647 bytes.

el siguiente código falla con

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

Sin embargo, parece que estas limitaciones se han eliminado silenciosamente. En 2008

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

Devoluciones

 8589767761 

Ejecuté esto en mi máquina de escritorio de 32 bits por lo que esta cadena de 8GB excede 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 páginas LOB en tempdb sin validación de longitud. El crecimiento del conteo de páginas estuvo asociado con SET @y = REPLICATE(@y,92681); statement. La asignación de variable inicial a @y el cálculo de LEN no aumentó esto.

La razón para mencionar esto es porque el recuento de páginas es mucho más de lo que esperaba. Asumiendo una página de 8KB, esto da un resultado de 16.36 GB, lo que obviamente es más o menos el doble de lo que parece ser necesario. Supongo que esto se debe probablemente a la ineficacia de la operación de concatenación de cadenas que necesita copiar toda la cadena enorme y agregar un fragmento al final en lugar de poder agregarlo al final de la cadena existente. Lamentablemente, en este momento, el método .WRITE no es compatible con variables varchar (max).

Adición

También probé el comportamiento con concatenar nvarchar(max) + nvarchar(max) y nvarchar(max) + varchar(max) . Ambos permiten superar el límite de 2 GB. Sin embargo, intentar almacenar los resultados de esto en una tabla falla, pero con el mensaje de error Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. de nuevo. La secuencia de comandos para eso está debajo (puede tomar mucho tiempo para 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 

EDITAR : Después de una investigación más profunda, mi suposición original de que se trataba de una anomalía (¿error?) De la declare @var datatype = value es incorrecta.

Modifiqué su secuencia de comandos para 2005 ya que esa syntax no es compatible, luego probé la versión modificada en 2008. En 2005, recibí el Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. mensaje de error. 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) 

puede almacenar hasta 4000 letras de texto en varchar en Oracle 10g.