Lobby pentru varchar
Datele de tip varchar pot fi de 2 tipuri:
- cele pentru care se precizeaza o limita de pana la 8000 caractere; acestea se vad cand pui varchar(n), cu n pana la 8000
- cele care care sunt "fara limita" intelegandu-se aici siruri de pana la 2Gb (2,147,483,647 bytes); se marcheaza cu varchar(max).
Mai jos vedeti niste linii de cod care verifica capacitatile varchar(max).
////////
declare @i as int
set @i=0
DECLARE @myVariable AS varchar(max)
SET @myVariable = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
--datalength of @myVariable is now 100
while @i <=10
begin
set @i=@i+1
set @myvariable=@myvariable+@myvariable
print 'Data length after concatenating data : '
print datalength(@myvariable)
end
////////
Iata ce rezultate da codul de mai sus cand @i este pana la 10:
Data length after concatenating data :
200
Data length after concatenating data :
400
Data length after concatenating data :
800
Data length after concatenating data :
1600
Data length after concatenating data :
3200
Data length after concatenating data :
6400
Data length after concatenating data :
12800
Data length after concatenating data :
25600
Data length after concatenating data :
51200
Data length after concatenating data :
102400
Dupa a 10-a iteratie @myvariable are dimensiunea 102400.
Daca relansam acest cod cu @i cel mult 100, computerul sta in jur de 10 minute si da urmatorul rezultat
...
Data length after concatenating data :
419430400
Data length after concatenating data :
838860800
Data length after concatenating data :
1677721600
Msg 7119, Level 16, State 1, Line 11
Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes.
Data length after concatenating data :
Msg 7102, Level 20, State 99, Line 13
Internal Error: Text manager cannot continue with current statement. Run DBCC CHECKTABLE.
Sistemul nu a mai putut face concatenarea pentru ca ar fi depasit 2,147,483,647 bytes.
Concluzia este ca atunci cand manevrati siruri de caractere puteti folosi varchar, care sunt mai manevrabile decat cele de tip "text", avand grija sa nu depasiti "cota" de spatiu distribuita.
Gheorghe Ciubuc,SQL Server Influencer, MCP(SQL 2000), MCTS (SQL Server 2005) , OCA(Oracle 9i), Sybase(Brainbench)