Динамический SQL-запрос не может хранить более 4000 символов даже с NVARCHAR (MAX) [duplicate]

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

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

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

nvarchar [ ( n | max ) ]

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

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

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

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

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

1. varchar(n) + varchar(n) будет усекаться с 8000 символами.
2. nvarchar(n) + nvarchar(n) усекает до 4000 символов.
3. varchar(n) + nvarchar(n) усекает до 4000 символов. nvarchar имеет более высокий приоритет, поэтому результат nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max) не будет усекать (для & lt; 2GB).
5. varchar(max) + varchar(n) не будет усекать (для & lt; 2 ГБ), и результат будет напечатан как varchar(max).
6. varchar(max) + nvarchar(n) выиграл 't обрезать (для & lt; 2GB), и результат будет напечатан как nvarchar(max).
7. nvarchar(max) + varchar(n) сначала преобразует вход varchar(n) в nvarchar(n), а затем выполнит конкатенацию. Если длина строки varchar(n) больше 4000 символов, то приведение будет nvarchar(4000) и произойдет усечение.

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

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

Если вы не используете префикс N, а строка длиной & lt; = 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'

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

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

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

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

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