Taille maximale d'une variable varchar(max)
à tout moment dans le passé, si quelqu'un m'avait demandé la taille maximale pour un varchar(max) , j'aurais dit 2 Go, ou cherché un plus exact chiffre (2^31-1, ou 2147483647).
cependant, dans certains tests récents, j'ai découvert que 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 - quelqu'un sait-il Quelle est la limite réelle pour une variable varchar(max) ?
(test ci-dessus complété sur SQL Server 2008 (pas R2). Je serais intéressé de savoir si elle s'applique à d'autres versions)
3 réponses
autant que je sache, il n'y a pas de limite supérieure en 2008.
dans SQL Server 2005 le code dans votre question échoue sur l'affectation à la variable @GGMMsg avec
tenter de faire pousser du LOB au-delà de la taille maximale autorisée de 2 147 483 647 octet.
le code ci-dessous ne correspond pas à
répliquer: la longueur du résultat dépasse la longueur limite (2 Go) de le grand type cible.
cependant, il semble que ces limitations ont été tranquillement levées. On 2008
DECLARE @y VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),92681);
SET @y = REPLICATE(@y,92681);
SELECT LEN(@y)
retourne
8589767761
j'ai lancé ceci sur ma machine de bureau 32 bits de sorte que cette chaîne de 8 Go est bien au-delà de la mémoire adressable
Running
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 présume tout cela est simplement stocké dans les pages LOB dans tempdb sans aucune validation de la longueur. La croissance du nombre de pages était associée à l'énoncé SET @y = REPLICATE(@y,92681); . L'attribution initiale de la variable @y et le calcul de la variable LEN n'ont pas augmenté cette valeur.
la raison de mentionner ceci est parce que le nombre de pages est considérablement plus que je ne m'y attendais. En supposant une page de 8KB alors cela revient à 16.36 GO ce 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 à l'heure actuelle, la .WRITE méthode n'est pas pris en charge pour varchar(max) variables.
Plus
j'ai aussi testé le comportement de la concaténation de nvarchar(max) + nvarchar(max) et nvarchar(max) + varchar(max) . Les deux permettent de dépasser la limite de 2 Go. 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 pour cela est ci-dessous (peut prendre beaucoup de temps à 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
EDIT : après une enquête plus approfondie, mon hypothèse initiale qu'il s'agissait d'une anomalie (bug?) de la syntaxe declare @var datatype = value est incorrecte.
j'ai modifié votre script pour 2005 puisque cette syntaxe n'est pas supportée, puis j'ai essayé la version modifiée en 2008. En 2005, je reçois le message d'erreur Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. . En 2008, le script modifié est encore 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)
vous pouvez stocker jusqu'à 4000 lettres de texte dans varchar dans oracle 10g.