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?
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.
-
varchar(n) + varchar(n)
sera tronqué à 8 000 caractères. -
nvarchar(n) + nvarchar(n)
sera tronqué à 4 000 caractères. -
varchar(n) + nvarchar(n)
sera tronqué à 4 000 caractères.nvarchar
a priorité plus élevée de sorte que le résultat estnvarchar(4,000)
-
[n]varchar(max)
+[n]varchar(max)
ne se tronquera pas (pour < 2 Go). -
varchar(max)
+varchar(n)
ne pas tronquer (<2 GO), et le résultat sera tapévarchar(max)
. -
varchar(max)
+nvarchar(n)
ne pas tronquer (<2 GO), et le résultat sera tapénvarchar(max)
. -
nvarchar(max)
+varchar(n)
va d'abord convertissez l'entréevarchar(n)
ennvarchar(n)
et faites la concaténation. si la longueur de la chaînevarchar(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)
où 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)
où 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.
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 <
.
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
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
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