Dimensione massima di una variabile varchar (max)

In qualsiasi momento, in passato, se qualcuno mi avesse chiesto la dimensione massima per un varchar(max) , avrei detto 2 GB, oppure ho cercato una figura più precisa (2 ^ 31-1 o 2147483647).

Tuttavia, in alcuni test recenti, ho scoperto che le variabili varchar(max) possono apparentemente superare questa dimensione:

 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 

risultati:

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

Quindi, dato che ora so che una variabile può superare la barriera da 2 GB, qualcuno sa qual è il limite effettivo per una variabile varchar(max) ?


(Completamento del test completato su SQL Server 2008 (non R2). Sarei interessato a sapere se si applica ad altre versioni)

Per quanto posso dire, non ci sono limiti massimi nel 2008.

In SQL Server 2005 il codice nella tua domanda non riesce sull’assegnazione alla variabile @GGMMsg con

Tentativo di crescere LOB oltre la dimensione massima consentita di 2.147.483.647 byte.

il codice qui sotto non funziona

RISPETTA: la lunghezza del risultato supera il limite di lunghezza (2 GB) del tipo grande di destinazione.

Tuttavia sembra che queste limitazioni siano state tranquillamente eliminate. Nel 2008

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

ritorna

 8589767761 

L’ho eseguito sul mio computer desktop a 32 bit, quindi questa stringa da 8 GB è decisamente eccessiva rispetto alla memoria indirizzabile

In esecuzione

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

tornati

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

quindi presumo che tutto questo venga memorizzato nelle pagine LOB in tempdb senza alcuna convalida sulla lunghezza. La crescita del numero di pagine è stata associata a SET @y = REPLICATE(@y,92681); dichiarazione. L’assegnazione della variabile iniziale a @y e il calcolo LEN non ha aumentato questo.

Il motivo per cui lo si menziona è perché il numero di pagine è enormemente superiore a quello che mi aspettavo. Supponendo una pagina da 8KB, questo funziona a 16,36 GB, che è ovviamente più o meno il doppio di ciò che sembra essere necessario. Suppongo che questo sia probabilmente dovuto all’inefficienza dell’operazione di concatenazione delle stringhe che richiede di copiare l’intera stringa enorme e di aggiungere un blocco alla fine piuttosto che essere in grado di aggiungere alla fine della stringa esistente. Sfortunatamente al momento il metodo .WRITE non è supportato per le variabili varchar (max).

aggiunta

Ho anche testato il comportamento con la concatenazione di nvarchar(max) + nvarchar(max) e nvarchar(max) + varchar(max) . Entrambi consentono il superamento del limite di 2 GB. Cercando di memorizzare i risultati di questo in una tabella, tuttavia, non riesce con il messaggio di errore Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. ancora. Lo script è sotto (potrebbe richiedere molto tempo per essere eseguito).

 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 : dopo ulteriori indagini, il mio assunto originale che si trattasse di un’anomalia (bug?) declare @var datatype = value syntax declare @var datatype = value non è corretto.

Ho modificato il tuo script per il 2005 poiché quella syntax non è supportata, quindi ho provato la versione modificata nel 2008. Nel 2005, ho ottenuto il Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. messaggio di errore. Nel 2008, lo script modificato ha ancora successo.

 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) 

è ansible memorizzare fino a 4000 lettere di testo in varchar in oracle 10g.