Limites de nvarchar et de VARCHAR

tout, j'ai une grande (inévitable) requête SQL dynamique. En raison du nombre de champs dans les critères de sélection, la chaîne contenant le SQL dynamique croît de plus de 4000 caractères. Maintenant, je comprends qu'il y a un 4000 max défini pour NVARCHAR(MAX) , mais en regardant le SQL exécuté dans le profileur de serveur pour la déclaration

DELARE @SQL NVARCHAR(MAX);
SET @SQL = 'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO

Semble fonctionner(!?), pour une autre requête qui est aussi grande il lance une erreur qui est associée à cette limite de 4000 (!?), il fondamentalement trime tout le SQL après cette limite de 4000 et me laisse avec une erreur de syntaxe. Malgré cela dans le profileur, il montre cette requête SQL dynamique dans full (!?).

que se passe-t-il exactement ici et devrais-je simplement convertir cette variable @SQL en VARCHAR et m'y mettre?

Merci pour votre temps.

Ps. Il serait également agréable d'être en mesure d'imprimer plus de 4000 caractères à regarder ces big requêtes. Sont limités à 4000

SELECT CONVERT(XML, @SQL);
PRINT(@SQL);

y a-t-il un autre moyen cool?

90
demandé sur SynozeN Technologies 2012-09-28 16:22:11

4 réponses

je comprends qu'il y a un 4,000 max set pour NVARCHAR(MAX)

Votre compréhension est erronée. nvarchar(max) peut stocker jusqu'à (et parfois au-delà) 2 Go de données (1 milliard de caractères à double octet).

à Partir de nchar et nvarchar dans la documentation en ligne de la grammaire est

nvarchar [ ( n | max ) ]

le caractère | signifie qu'il s'agit d'alternatives. c'est à dire que vous spécifiez soit n , soit au sens littéral max .

si vous choisissez de spécifier un n spécifique, alors cela doit être entre 1 et 4.000, mais en utilisant max le définit comme un type de données Grand objet (remplacement de ntext qui est déprécié).

en fait dans SQL Server 2008, il semble que pour une variable la limite de 2 Go peut être dépassée indéfiniment sous réserve d'espace suffisant dans tempdb ( indiqué ici )

concernant les autres parties de votre question

troncature lors de la concaténation dépend du type de données.

  1. varchar(n) + varchar(n) sera tronqué à 8 000 caractères.
  2. nvarchar(n) + nvarchar(n) sera tronqué à 4 000 caractères.
  3. varchar(n) + nvarchar(n) sera tronqué à 4 000 caractères. nvarchar a priorité plus élevée de sorte que le résultat est nvarchar(4,000)
  4. [n]varchar(max) + [n]varchar(max) ne se tronquera pas (pour < 2 Go).
  5. varchar(max) + varchar(n) ne pas tronquer (<2 GO), et le résultat sera tapé varchar(max) .
  6. varchar(max) + nvarchar(n) ne pas tronquer (<2 GO), et le résultat sera tapé nvarchar(max) .
  7. nvarchar(max) + varchar(n) va d'abord convertissez l'entrée varchar(n) en nvarchar(n) et faites la concaténation. si la longueur de la chaîne varchar(n) est supérieure à 4 000 caractères, la fonte sera à nvarchar(4000) et la troncature se produira .

les Types de données de chaîne de caractères littéraux

si vous utilisez le préfixe N et que la chaîne est <= 4 000 caractères, elle sera dactylographiée comme nvarchar(n)n est la longueur de la chaîne. Ainsi N'Foo' sera traité comme nvarchar(3) par exemple. Si la chaîne est plus longue que 4.000 caractères, elle sera traitée comme nvarchar(max)

si vous n'utilisez pas le préfixe N et que la chaîne est de moins de 8 000 caractères, elle sera tapée comme varchar(n)n est la longueur de la chaîne. Si plus long que varchar(max)

pour les deux ci-dessus si la longueur de la chaîne est zéro alors n est réglé à 1.

"1519860920 de" Nouveaux éléments de syntaxe.

1. la fonction CONCAT n'aide pas ici

DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000);

SELECT DATALENGTH(@A5000 + @A5000), 
       DATALENGTH(CONCAT(@A5000,@A5000));

ce qui précède renvoie 8000 pour les deux méthodes de concaténation.

2. faites attention avec +=

DECLARE @A VARCHAR(MAX) = '';

SET @A+= REPLICATE('A',5000) + REPLICATE('A',5000)

DECLARE @B VARCHAR(MAX) = '';

SET @B = @B + REPLICATE('A',5000) + REPLICATE('A',5000)


SELECT DATALENGTH(@A), 
       DATALENGTH(@B);`

retourne

-------------------- --------------------
8000                 10000

noter que @A a été tronqué.

Comment résoudre le problème que vous rencontrez.

vous obtenez la troncature soit parce que vous concaténez deux types de données non max ensemble, soit parce que vous concaténez une chaîne de caractères varchar(4001 - 8000) à une chaîne de caractères nvarchar (même nvarchar(max) ).

pour éviter la deuxième question, assurez-vous simplement que toutes les chaînes de caractères la gamme 4001 - 8000) sont précédés de N .

pour éviter la première émission, changer l'affectation de

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'Foo' + 'Bar' + ...;

à

DECLARE @SQL NVARCHAR(MAX) = ''; 
SET @SQL = @SQL + N'Foo' + N'Bar'

de sorte qu'un NVARCHAR(MAX) est impliqué dans la concaténation dès le début (car le résultat de chaque concaténation sera également NVARCHAR(MAX) ce qui se propagera)

éviter la troncature lors de l'observation

assurez-vous que vous avoir le mode" résultats à la grille "sélectionné puis vous pouvez utiliser

select @SQL as [processing-instruction(x)] FOR XML PATH 

les options SSMS vous permettent de définir une longueur illimitée pour les résultats XML . Le bit processing-instruction évite les problèmes avec des caractères tels que < apparaissant comme &lt; .

212
répondu Martin Smith 2017-05-23 12:10:31

Ok, donc si plus tard dans la ligne le problème est que vous avez une requête qui est plus grande que la taille autorisée (ce qui peut arriver si elle continue à croître) vous allez devoir la casser en morceaux et exécuter les valeurs de chaîne de caractères. Donc, disons que vous avez une procédure stockée comme suit:

CREATE PROCEDURE ExecuteMyHugeQuery
    @SQL VARCHAR(MAX) -- 2GB size limit as stated by Martin Smith
AS
BEGIN
    -- Now, if the length is greater than some arbitrary value
    -- Let's say 2000 for this example
    -- Let's chunk it
    -- Let's also assume we won't allow anything larger than 8000 total
    DECLARE @len INT
    SELECT @len = LEN(@SQL)

    IF (@len > 8000)
    BEGIN
        RAISERROR ('The query cannot be larger than 8000 characters total.',
                   16,
                   1);
    END

    -- Let's declare our possible chunks
    DECLARE @Chunk1 VARCHAR(2000),
            @Chunk2 VARCHAR(2000),
            @Chunk3 VARCHAR(2000),
            @Chunk4 VARCHAR(2000)

    SELECT @Chunk1 = '',
           @Chunk2 = '',
           @Chunk3 = '',
           @Chunk4 = ''

    IF (@len > 2000)
    BEGIN
        -- Let's set the right chunks
        -- We already know we need two chunks so let's set the first
        SELECT @Chunk1 = SUBSTRING(@SQL, 1, 2000)

        -- Let's see if we need three chunks
        IF (@len > 4000)
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, 2000)

            -- Let's see if we need four chunks
            IF (@len > 6000)
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, 2000)
                SELECT @Chunk4 = SUBSTRING(@SQL, 6001, (@len - 6001))
            END
              ELSE
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, (@len - 4001))
            END
        END
          ELSE
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, (@len - 2001))
        END
    END

    -- Alright, now that we've broken it down, let's execute it
    EXEC (@Chunk1 + @Chunk2 + @Chunk3 + @Chunk4)
END
6
répondu Mike Perrenoud 2012-09-28 12:41:51

vous devez aussi utiliser du texte nvarchar. cela signifie que vous devez simplement avoir un " N " avant votre chaîne massive et c'est tout! plus de limitation

DELARE @SQL NVARCHAR(MAX);
SET @SQL = N'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO
2
répondu Max 2015-06-18 07:32:35
declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 100000)

 insert @local values(cast(@p as varchar(max)))
 select DATALENGTH(col) as collen, col from @local

result collen > 8000, length col value is more than 8000 chars
0
répondu Heta77 2017-03-23 09:35:54