Ограничения SQL NVARCHAR и VARCHAR

Ответ 1

Я понимаю, что для NVARCHAR(MAX)

существует 4000 максимальных значений,

Ваше понимание неверно. NVARCHAR(MAX) может хранить до (и за пределами иногда) 2 ГБ данных (1 млрд. двухбайтовых символов).

Из nchar и nvarchar в онлайн-книгах грамматика

nvarchar [ ( n | max ) ]

Символ | означает, что это альтернативы. то есть вы указываете либо n, либо литерал max.

Если вы решите указать конкретный n, то это должно быть от 1 до 4000, но с использованием max определяет его как большой тип данных объекта (замена ntext, который устарел).

Фактически в SQL Server 2008 кажется, что для переменной ограничение 2 ГБ может быть неограниченно превышено на достаточное пространство в tempdb (показано здесь)

Что касается других частей вашего вопроса

Усечение при конкатенации зависит от типа данных.

• varchar(n) + varchar(n) будет усекаться с 8000 символами.
• nvarchar(n) + nvarchar(n) будет усекаться с 4000 символов.
• varchar(n) + nvarchar(n) будет обрезаться с 4000 символов. nvarchar имеет более высокий приоритет, поэтому результат nvarchar(4,000)
• [n]varchar(max) + [n]varchar(max) не будет усекать (для < 2GB).
• varchar(max) + varchar(n) не будет усекать (для < 2GB), и результат будет напечатан как varchar(max).
• varchar(max) + nvarchar(n) не будет усекать (для < 2GB), и результат будет напечатан как NVARCHAR(MAX).
• NVARCHAR(MAX) + varchar(n) сначала преобразует вход varchar(n) в nvarchar(n), а затем выполнит конкатенацию. Если длина строки varchar(n) больше 4000 символов, то приведение будет nvarchar(4000) и произойдет усечение.

Типы данных строковых литералов

Если вы используете префикс n, а строка - <= 4000 символов, он будет набираться как nvarchar(n), где n - длина строки. Таким образом, N'Foo' будет рассматриваться как nvarchar(3), например. Если строка длиннее 4000 символов, она будет обрабатываться как NVARCHAR(MAX)

Если вы не используете префикс n, а строка - <= 8000 символов, он будет набираться как varchar(n), где n - длина строки. Если дольше varchar(max)

Для обоих указанных выше, если длина строки равна нулю, тогда n устанавливается в 1.

Новые элементы синтаксиса.

1. Функция CONCAT здесь не помогает

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

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

Приведенное выше возвращает 8000 для обоих методов конкатенации.

2. Будьте осторожны с +=

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

Возвращает

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

Обратите внимание, что @A встретил усечение.

Как решить проблему, с которой вы столкнулись.

Вы получаете усечение либо из-за того, что вы объединяете два типа данных типа max, либо из-за того, что вы объединяете строку varchar(4001 - 8000) с введенной строкой nvarchar (даже NVARCHAR(MAX)).

Чтобы избежать второй проблемы, просто убедитесь, что все строковые литералы (или, по крайней мере, те, которые имеют длину в диапазоне 4001-8000) имеют префикс n.

Чтобы избежать первой проблемы, измените назначение из

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

Для

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

так что a NVARCHAR(MAX) участвует в конкатенации с самого начала (так как результат каждой конкатенации будет также NVARCHAR(MAX), это будет распространяться)

Избегание усечения при просмотре

Убедитесь, что выбран режим "результаты в сетку", и вы можете использовать

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

Параметры SSMS позволяют устанавливать неограниченную длину для результатов XML. Бит processing-instruction позволяет избежать проблем с такими символами, как <, отображаемый как <.

Ответ 2

Хорошо, поэтому, если позже в строке, проблема заключается в том, что у вас есть запрос, который превышает допустимый размер (что может произойти, если оно продолжает расти), вам придется сломать это в куски и выполнить строковые значения. Итак, скажем, у вас есть хранимая процедура, например:

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 say 2000 for this example
    -- Let chunk it
    -- Let 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 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 set the right chunks
        -- We already know we need two chunks so let set the first
        SELECT @Chunk1 = SUBSTRING(@SQL, 1, 2000)

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

            -- Let 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 execute it
    EXEC (@Chunk1 + @Chunk2 + @Chunk3 + @Chunk4)
END

Ответ 3

Вы также используете текст nvarchar. это означает, что вы должны просто иметь "N" перед своей массивной струной и это! больше никаких ограничений

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

Ответ 4

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

Ответ 5

Принятый ответ помог мне, но я был сбит с толку, когда делал конкатенацию varchars, включающую заявления случая. Я знаю, что вопрос OP не включает в себя операторы case, но я подумал, что было бы полезно опубликовать здесь для таких, как я, которые оказались здесь, пытаясь создать длинные динамические операторы SQL, включающие операторы case.

При использовании операторов case с конкатенацией строк правила, указанные в принятом ответе, применяются независимо к каждому разделу оператора case.

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)