Limity SQL NVARCHAR i VARCHAR

Question

Limity SQL NVARCHAR i VARCHAR

Wszystko, mam duże (nieuniknione) dynamiczne zapytanie SQL. Ze względu na ilość pól w kryteriach wyboru ciąg zawierający dynamiczny SQL rośnie o ponad 4000 znaków. Teraz rozumiem, że jest ustawiony max 4000 dla NVARCHAR(MAX), ale patrząc na wykonywany SQL W Server Profiler dla instrukcji

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

Wydaje się działać (!?), dla innego zapytania, które jest również duże rzuca błąd, który jest związany z tym limitem 4000 (!?), to w zasadzie przycina cały SQL po tym 4000 limit i pozostawia mi błąd składni. Pomimo tego w profilerze, wyświetla to dynamiczne zapytanie SQL w full (!?).

Co dokładnie się tutaj dzieje i czy mam po prostu konwertować tę zmienną @SQL na VARCHAR i zaczynać?

Dziękuję za poświęcony czas.

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

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

Jest jakiś inny fajny sposób?

90

sql variables sql-server string-concatenation sql-server-2008

Author: SynozeN Technologies, 2012-09-28

Source

4 answers

Okay, więc jeśli później w dół linii problem polega na tym, że masz zapytanie, które jest większe niż dopuszczalny rozmiar (co może się zdarzyć, jeśli będzie rosnąć) będziesz musiał rozbić je na kawałki i wykonać wartości ciągu. Załóżmy więc, że masz następującą procedurę składowaną:

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

Author: Mike Perrenoud,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-28 12:41:51

You mus use nvarchar text too. to znaczy, że musisz po prostu mieć " N " przed swoim masywnym sznurkiem i to wszystko! no limitation anymore

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

2

Author: Max,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
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

Author: Heta77,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-23 09:35:54

score 212 · Accepted Answer

Rozumiem, że jest zestaw 4000 max dla NVARCHAR(MAX)

Twoje zrozumienie jest złe. nvarchar(max) może przechowywać do 2 GB danych (czasem nawet więcej) (1 miliard znaków dwubajtowych).

Z nchar i nvarchar w książkach online gramatyka jest

nvarchar [ ( n | max ) ]

Znak | oznacza, że są to alternatywy. tzn. podajesz albo n lub dosłowne max.

Jeśli zdecydujesz się podać konkretny n to musi być od 1 do 4000, ale używając max definiuje go jako duży obiektowy typ danych (zamiennik dla ntext który jest przestarzały).

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

Odnośnie pozostałych części twojego pytania

Okrojenie podczas łączenia zależy od typu danych.

/ Align = "left" / postaci.
nvarchar(n) + nvarchar(n) skróci się o 4000 znaków.
varchar(n) + nvarchar(n) skróci się o 4000 znaków. nvarchar ma wyższy priorytet, więc wynik jest nvarchar(4,000)
[n]varchar(max) + [n]varchar(max) nie będzie obcinać (dla
varchar(max) + varchar(n) nie będzie obcinać (dla varchar(max).
varchar(max) + nvarchar(n) nie będzie obcinać (dla nvarchar(max).
nvarchar(max) + varchar(n) najpierw przekonwertuje wejście varchar(n) na nvarchar(n) a następnie wykonaj konkatenację. jeśli długość varchar(n) ciągu znaków jest większa niż 4000 znaków, rzut będzie nvarchar(4000) i nastąpi obcięcie .

Datatypy liter ciągów

Jeśli użyjesz prefiksu N i łańcuch będzie miał długość nvarchar(n), gdzie n jest długością łańcucha. Więc N'Foo' będą traktowane jako nvarchar(3) na przykład. Jeśli łańcuch jest dłuższy niż 4000 znaków będzie traktowany jako nvarchar(max)

Jeśli nie użyjesz prefiksu N, a łańcuch ma długość varchar(n), gdzie n jest długością łańcucha. If longer as varchar(max)

Dla obu powyższych, jeśli długość łańcucha wynosi zero, to {[10] } jest ustawione na 1.

Nowsze elementy składni.

1. funkcja CONCAT tutaj nie pomaga

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

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

Powyższe zwraca 8000 dla obu metod konkatenacji.

2. Be ostrożnie 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);`

Zwraca

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

Zauważ, że @A napotkał okrojenie.

Jak rozwiązać problem, którego doświadczasz.

Dostajesz obcinanie albo dlatego, że łączysz dwa nie max typy danych razem, albo dlatego, że łączysz varchar(4001 - 8000) łańcuch do nvarchar wpisany łańcuch (nawet nvarchar(max)).

Aby uniknąć drugiego problemu, po prostu upewnij się, że wszystkie literały ciągu (lub przynajmniej te o długościach w 4001 - Zakres 8000) są poprzedzone N.

Aby uniknąć pierwszego problemu Zmień Przypisanie z

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

Do

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

W wyniku tego, w wyniku każdej konkatenacji będzie ona również propagowana).]}

Unikanie obcinania podczas oglądania

Upewnij się, że masz wybrany tryb "results to grid", a następnie możesz użyć

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

Opcje SSMS pozwalają ustawić nieograniczoną długość dla XML wyniki. Bit processing-instruction pozwala uniknąć problemów ze znakami takimi jak < pojawiającymi się jako <.