Welcome to Sign in | Join | Help

Lobby pentru varchar

  •  10-14-2006, 10:21 AM

    Lobby pentru varchar

    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)
    Filed under:
View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems