SQL NVARCHAR- und VARCHAR-Grenzwerte
Ich habe alle eine große (unvermeidliche) dynamische SQL-Abfrage. Aufgrund der Anzahl der Felder in den Auswahlkriterien wächst die Zeichenfolge, die das dynamische SQL enthält, auf über 4000 Zeichen. Jetzt verstehe ich, dass für NVARCHAR(MAX) ein maximaler Satz von 4000 festgelegt ist, aber die ausgeführte SQL in Server Profiler für die Anweisung betrachtet
DELARE @SQL NVARCHAR(MAX);
SET @SQL = 'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO
Scheint zu funktionieren (!?), Für eine andere Abfrage, die ebenfalls groß ist, wirft sie einen Fehler aus, der mit diesem 4000-Grenzwert (!?) verbunden ist. Im Wesentlichen werden alle SQL-Werte nach diesem 4000-Grenzwert abgeschnitten und es wird ein Syntaxfehler angezeigt. Trotzdem zeigt der Profiler diese dynamische SQL-Abfrage in full (!?).
Was genau passiert hier und sollte ich diese @SQL-Variable einfach in VARCHAR konvertieren und weitermachen?
Vielen Dank für Ihre Zeit.
Ps. Es wäre auch schön, mehr als 4000 Zeichen drucken zu können, um diese großen Abfragen zu betrachten. Die folgenden sind auf 4000 begrenzt
SELECT CONVERT(XML, @SQL);
PRINT(@SQL);
gibt es einen anderen coolen Weg?
Ich verstehe, dass es ein Maximum von 4000 für
NVARCHAR(MAX)gibt.
Dein Verständnis ist falsch. nvarchar(max) kann bis zu (und manchmal auch darüber hinaus) 2 GB Daten speichern (1 Milliarde Doppelbyte-Zeichen).
Aus nchar und nvarchar in Books Online ist die Grammatik
nvarchar [ ( n | max ) ]
Das Zeichen | bedeutet, dass dies Alternativen sind. Sie geben also entwedern oder das Literal max an.
Wenn Sie eine bestimmte n angeben, muss diese zwischen 1 und 4.000 liegen. Wenn Sie max verwenden, wird dies jedoch als großer Objektdatentyp definiert (Ersetzung für ntext , das veraltet ist).
In SQL Server 2008 scheint es jedoch so zu sein, dass für eine Variable das 2-GB-Limit unbegrenzt überschritten werden kann, vorausgesetzt, es ist ausreichend Speicherplatz in tempdb ( hier gezeigt )
Zu den anderen Teilen Ihrer Frage
Die Verkürzung beim Verketten hängt vom Datentyp ab.
varchar(n) + varchar(n)wird bei 8.000 Zeichen abgeschnitten.nvarchar(n) + nvarchar(n)wird bei 4.000 Zeichen abgeschnitten.varchar(n) + nvarchar(n)wird bei 4.000 Zeichen abgeschnitten.nvarcharhat eine höhere Priorität, das Ergebnis istnvarchar(4,000)[n]varchar(max)+[n]varchar(max)wird nicht gekürzt (für <2 GB).varchar(max)+varchar(n)wird nicht gekürzt (für <2 GB) und das Ergebnis wird alsvarchar(max)eingegeben.varchar(max)+nvarchar(n)wird nicht gekürzt (für <2 GB) und das Ergebnis wird alsnvarchar(max)eingegeben.nvarchar(max)+varchar(n)konvertiert zuerst dievarchar(n)-Eingabe innvarchar(n)und führt dann die Verkettung durch. Wenn die Länge dervarchar(n)-Zeichenfolge mehr als 4.000 Zeichen beträgt, lautet die Umwandlung innvarchar(4000)und die Kürzung erfolgt.
Datentypen von String-Literalen
Wenn Sie das Präfix N verwenden und der String <= 4.000 Zeichen lang ist, wird er als nvarchar(n) eingegeben, wobei n die Länge des Strings ist. So wird N'Foo' zum Beispiel als nvarchar(3) behandelt. Wenn die Zeichenfolge länger als 4.000 Zeichen ist, wird sie als nvarchar(max) behandelt.
Wenn Sie nicht das Präfix N verwenden und der String <= 8.000 Zeichen lang ist, wird er als varchar(n) eingegeben, wobei n die Länge des Strings ist. Wenn länger als varchar(max)
Für beide der obigen Fälle ist n auf 1 gesetzt, wenn die Länge der Zeichenfolge Null ist.
Neuere Syntaxelemente.
1. Die CONCAT-Funktion hilft hier nicht
DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000);
SELECT DATALENGTH(@A5000 + @A5000),
DATALENGTH(CONCAT(@A5000,@A5000));
Das obige gibt 8000 für beide Verkettungsmethoden zurück.
2. Sei vorsichtig mit +=
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);`
Kehrt zurück
-------------------- --------------------
8000 10000
Beachten Sie, dass @A eine Kürzung festgestellt hat.
So lösen Sie das aufgetretene Problem.
Sie werden abgeschnitten, weil Sie entweder zwei nicht max-Datentypen miteinander verketten oder weil Sie eine varchar(4001 - 8000)-Zeichenfolge mit einer nvarchar-Zeichenfolge verketten (auch nvarchar(max)).
Um das zweite Problem zu vermeiden, stellen Sie sicher, dass allen Stringliteralen (oder zumindest den mit Längen im Bereich 4001 - 8000) N vorangestellt wird.
Um das erste Problem zu vermeiden, ändern Sie die Zuordnung von
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'Foo' + 'Bar' + ...;
Zu
DECLARE @SQL NVARCHAR(MAX) = '';
SET @SQL = @SQL + N'Foo' + N'Bar'
so dass eine NVARCHAR(MAX) von Anfang an in die Verkettung involviert ist (als Ergebnis jeder Verkettung wird auch NVARCHAR(MAX) sein)
Vermeiden Sie das Abschneiden beim Anzeigen
Vergewissern Sie sich, dass Sie den Modus "Ergebnisse in Raster" ausgewählt haben, den Sie verwenden können
select @SQL as [processing-instruction(x)] FOR XML PATH
Mit den SSMS-Optionen können Sie eine unbegrenzte Länge für XML-Ergebnisse festlegen. Das processing-instruction-Bit vermeidet Probleme mit Zeichen wie <, die als < angezeigt werden.
Okay, wenn später auf der ganzen Linie das Problem ist, dass Sie eine Abfrage haben, die größer als die zulässige Größe ist (was passieren kann, wenn sie weiter wächst), müssen Sie sie in Brocken aufteilen und das ausführen String-Werte. Nehmen wir an, Sie haben eine gespeicherte Prozedur wie die folgende:
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
Sie müssen auch nvarchar-Text verwenden. Das bedeutet, dass Sie einfach ein "N" vor Ihrer massiven Saite haben mussten und das war's! keine Einschränkung mehr
DELARE @SQL NVARCHAR(MAX);
SET @SQL = N'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO
Die akzeptierte Antwort hat mir geholfen, aber ich bin beim Verketten von Varchars mit case-Anweisungen gestolpert. Ich weiß, dass die Frage des OP keine case-Anweisungen beinhaltet, aber ich dachte, dies wäre hilfreich, um sie hier für andere wie mich zu posten, die hier gelandet sind und Schwierigkeiten haben, lange dynamische SQL-Anweisungen mit case-Anweisungen zu erstellen.
Bei der Verwendung von case-Anweisungen mit String-Verkettung gelten die in der akzeptierten Antwort genannten Regeln für jeden Abschnitt der case-Anweisung unabhängig.
declare @l_sql varchar(max) = ''
set @l_sql = @l_sql +
case when 1=1 then
--without this correction the result is truncated
--CONVERT(VARCHAR(MAX), '')
+REPLICATE('1', 8000)
+REPLICATE('1', 8000)
end
print len(@l_sql)
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