Limity SQL NVARCHAR i VARCHAR

Wszystko, mam duże (nieuniknione) dynamiczne zapytanie SQL. Ze względu na liczbę pól w kryteriach selekcji ciąg znaków zawierający dynamiczny SQL rośnie o ponad 4000 znaków. Teraz rozumiem, że istnieje ustawione maks. 4000 dla NVARCHAR(MAX), ale patrząc na wykonanie instrukcji SQL w Server Profiler dla instrukcji

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

Wydaje się, że działa (!?), dla innego zapytania, które również jest duże, generuje błąd, który jest powiązany z tym limitem 4000 (!?), w zasadzie obcina cały SQL po tym limicie 4000 i zostawia mnie z błędem składniowym. Mimo to w profilerze pokazuje to dynamiczne zapytanie SQL w całości (!?).

Co dokładnie się tutaj dzieje i czy powinienem po prostu przekonwertować tę zmienną @SQL na VARCHAR i zająć się tym?

Dziękuję za Twój czas.

Ps. Byłoby również miło móc wydrukować ponad 4000 znaków, aby spojrzeć na te duże zapytania. Poniższe są ograniczone do 4000

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

czy jest jakiś inny fajny sposób?

Rozumiem, że jest ustawiony maksymalny 4000 NVARCHAR(MAX)

Twoje rozumienie jest błędne. nvarchar(max)może przechowywać do (a czasami więcej) 2 GB danych (1 miliard znaków dwubajtowych).

Od nchar i nvarchar w Book online gramatyka jest

nvarchar [ ( n | max ) ]

Te |środki te mają charakter alternatywy. tzn. określasz albo n albo literał max.

Jeśli zdecydujesz się określić konkretny, nmusi to być od 1 do 4000, ale użycie maxdefiniuje go jako typ danych dużego obiektu (zamiennik ntextjest przestarzały).

W rzeczywistości w SQL Server 2008 wydaje się, że dla zmiennej limit 2 GB może zostać przekroczony w nieskończoność, z zastrzeżeniem wystarczającej ilości miejsca w tempdb( pokazane tutaj )

Odnośnie innych części twojego pytania

Obcinanie przy konkatenacji zależy od typu danych.

1. varchar(n) + varchar(n) zostanie obcięty do 8 000 znaków.
2. nvarchar(n) + nvarchar(n) zostanie obcięty do 4000 znaków.
3. varchar(n) + nvarchar(n)zostanie obcięty do 4000 znaków. nvarcharma wyższy priorytet, więc wynik jestnvarchar(4,000)
4. [n]varchar(max)+ [n]varchar(max)nie zostanie obcięty (dla <2 GB).
5. varchar(max)+ varchar(n)nie zostanie obcięty (dla <2 GB), a wynik zostanie wpisany jako varchar(max).
6. varchar(max)+ nvarchar(n)nie zostanie obcięty (dla <2 GB), a wynik zostanie wpisany jako nvarchar(max).
7. nvarchar(max)+ varchar(n)najpierw przekonwertuje dane varchar(n)wejściowe, nvarchar(n)a następnie dokona konkatenacji. Jeśli długość varchar(n)ciągu jest większa niż 4000 znaków, rzutowanie zostanie skierowane na nvarchar(4000)i nastąpi obcięcie .

Typy danych literałów łańcuchowych

Jeśli użyjesz Nprefiksu, a ciąg ma <= 4000 znaków, zostanie on wpisany jako nvarchar(n)gdzie njest długością ciągu. Tak N'Foo'będzie traktowane jak nvarchar(3)np. Jeśli ciąg jest dłuższy niż 4000 znaków, zostanie potraktowany jakonvarchar(max)

Jeśli nie użyjesz Nprefiksu, a ciąg ma <= 8000 znaków, zostanie on wpisany jako varchar(n)gdzie njest długością łańcucha. Jeśli dłużej jakvarchar(max)

Dla obu powyższych, jeśli długość łańcucha wynosi zero, to njest ustawiana na 1.

Nowsze elementy składni.

1.CONCAT funkcja nie pomaga tutaj

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

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

Powyższe zwraca 8000 dla obu metod konkatenacji.

2. Uważaj z+=

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);`

Zwroty

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

Zauważ, że @Anapotkano obcięcie.

Jak rozwiązać napotkany problem.

Otrzymujesz obcięcie, ponieważ łączysz ze sobą dwa maxtypy niebędące typami danych lub ponieważ łączysz varchar(4001 - 8000)łańcuch z nvarcharwpisanym ciągiem (parzystym nvarchar(max)).

Aby uniknąć drugiego problemu, po prostu upewnij się, że wszystkie literały łańcuchowe (lub przynajmniej te o długościach z zakresu 4001 - 8000) są poprzedzone N.

Aby uniknąć pierwszego problemu, zmień przydział z

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

Do

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

tak, aby an NVARCHAR(MAX)był zaangażowany w konkatenację od początku (w wyniku każdego konkatenacji będzie to również się NVARCHAR(MAX)propagować)

Unikanie obcinania podczas przeglądania

Upewnij się, że masz wybrany tryb „wyniki do siatki”, a następnie możesz go użyć

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

Opcje SSMS pozwalają ustawić nieograniczoną długość XMLwyników. Ten processing-instructionbit pozwala uniknąć problemów ze znakami, takimi jak <wyświetlanie jako <.

Okay, więc jeśli później problem będzie polegał na tym, że masz zapytanie, które jest większe niż dopuszczalny rozmiar (co może się zdarzyć, jeśli będzie rosło), będziesz musiał podzielić je na fragmenty i wykonać wartości ciągu. Załóżmy więc, że masz procedurę składowaną, taką jak:

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

Musisz też użyć tekstu nvarchar. to znaczy, że musisz po prostu mieć „N” przed swoją masywną struną i to wszystko! nie ma już ograniczeń

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

Zaakceptowana odpowiedź pomogła mi, ale potknąłem się podczas wykonywania konkatenacji varcharów obejmujących opisy przypadków. Wiem, że pytanie OP nie obejmuje opisów przypadków, ale pomyślałem, że byłoby to pomocne dla innych, takich jak ja, którzy znaleźli się tutaj, walcząc z budowaniem długich dynamicznych instrukcji SQL zawierających opisy przypadków.

W przypadku stosowania instrukcji case z konkatenacją ciągów zasady wymienione w zaakceptowanej odpowiedzi mają zastosowanie do każdej sekcji opisu przypadku niezależnie.

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