Taille maximale d'un type varchar(max) de la variable

Question

À tout moment dans le passé, si quelqu'un m'avait demandé la taille maximale pour un varchar(max), j'aurais dit 2 go, ou regardé plus exacte de la figure (2^31-1, ou de 2 147 483 647).

Cependant, dans certaines études récentes, j'ai découvert qu' varchar(max) variables peuvent apparemment dépasser cette taille:

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

Résultats:

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

Donc, étant donné que je sais maintenant qu'une variable peut dépasser la barrière de 2 go - personne ne sait ce que la limite réelle est pour un varchar(max) variable?

---

(Test ci-dessus terminée sur SQL Server 2008 (non R2). Je serais intéressé de savoir si elle s'applique à d'autres versions)

Answer 1

Aussi loin que je peux voir, il n'y a pas de limite supérieure en 2008.

Dans SQL Server 2005, le code dans votre question échoue sur la cession à l' @GGMMsg variable avec

Qui tentent de se développer LOB au-delà de la taille maximale autorisée de 2 147 483 647 octets.

le code ci-dessous échoue avec

RÉPLIQUER: la longueur de La résultat est supérieur à la limite de longueur (2 go) de la cible grand type.

Cependant, il semble que ces limitations ont discrètement été levé. Sur 2008

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

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

SELECT LEN(@y) 

Retourne

8589767761

J'ai couru sur mon 32 bits ordinateur de bureau donc c'8 GO de chaîne est dans l'excès de mémoire adressable

L'exécution de

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

Retourné

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

donc je suppose que ce que tout ça est stocké dans LOB pages en tempdb sans validation sur la longueur. Le nombre de pages de la croissance a été de tous les associés à l' SET @y = REPLICATE(@y,92681); déclaration. La première affectation de variable d' @y et de la LEN calcul n'a pas augmenté.

La raison de mentionner c'est parce que le nombre de pages est énormément plus que ce que j'attendais. En supposant un 8KO page, alors il s'agit d'16.36 GO qui est évidemment plus ou moins le double de ce qui semble être nécessaire. Je suppose que cela est probablement dû à l'inefficacité de la chaîne opération de concaténation avoir besoin de copier la totalité de l'énorme chaîne de caractères et ajouter un morceau à la fin plutôt que d'être en mesure d'ajouter à la fin de la chaîne. Malheureusement, au moment de l' .WRITE méthode n'est pas pris en charge pour le type varchar(max) variables.

Plus

J'ai aussi testé le comportement avec la concaténation nvarchar(max) + nvarchar(max) et nvarchar(max) + varchar(max). Ces deux permettent la limite de 2 go à être dépassé. Essayer ensuite de stocker les résultats dans un tableau puis échoue cependant avec le message d'erreur Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. de nouveau. Le script de ce qui est en dessous (cela peut prendre du temps à s'exécuter).

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

Answer 2

EDIT: Après enquête, mon hypothèse initiale que c'était une anomalie (bug?) de la declare @var datatype = value syntaxe est incorrecte.

J'ai modifié le script de 2005 depuis que la syntaxe n'est pas pris en charge, ensuite essayé la version modifiée sur 2008. En 2005, j'ai l' Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. message d'erreur. En 2008, le script modifié est toujours un succès.

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)