SQLNVARCHAR 和 VARCHAR 限制

总之，我有一个大的(不可避免的)动态 SQL 查询。由于选择条件中字段的数量，包含动态 SQL 的字符串正在增长超过4000个字符。现在，我知道 NVARCHAR(MAX)的最大值是4000，但是请查看服务器事件探查器中执行的 SQL 语句

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

Seems to work(!?), for another query that is also large it throws an error which is associated with this 4000 limit(!?), it basically trims all of the SQL after this 4000 limit and leaves me with a syntax error. Despite this in the profiler, it is showing this dynamic SQL query in 满了(!?).

这里到底发生了什么，我是否应该只是将@SQL 变量转换为 VARCHAR 并继续下去？

谢谢你抽出时间。

P.如果能够打印出超过4000个字符来查看这些大型查询，那也是很不错的。以下限制为4000

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

还有别的好办法吗？

190777 次浏览
小开
最佳答案

I understand that there is a 4000 max set for NVARCHAR(MAX)

Your understanding is wrong. nvarchar(max) can store up to (and beyond sometimes) 2GB of data (1 billion double byte characters).

From nchar and nvarchar in Books online the grammar is

nvarchar [ ( n | max ) ]

The | character means these are alternatives. i.e. you specify either n or the literal max.

If you choose to specify a specific n then this must be between 1 and 4,000 but using max defines it as a large object datatype (replacement for ntext which is deprecated).

In fact in SQL Server 2008 it seems that for a variable the 2GB limit can be exceeded indefinitely subject to sufficient space in tempdb (Shown here)

Regarding the other parts of your question

Truncation when concatenating depends on datatype.

  1. varchar(n) + varchar(n) will truncate at 8,000 characters.
  2. nvarchar(n) + nvarchar(n) will truncate at 4,000 characters.
  3. varchar(n) + nvarchar(n) will truncate at 4,000 characters. nvarchar has higher precedence so the result is nvarchar(4,000)
  4. [n]varchar(max) + [n]varchar(max) won't truncate (for < 2GB).
  5. varchar(max) + varchar(n) won't truncate (for < 2GB) and the result will be typed as varchar(max).
  6. varchar(max) + nvarchar(n) won't truncate (for < 2GB) and the result will be typed as nvarchar(max).
  7. nvarchar(max) + varchar(n) will first convert the varchar(n) input to nvarchar(n) and then do the concatenation. If the length of the ABC1 string is greater than 4,000 characters the cast will be to nvarchar(4000) and truncation will occur.

Datatypes of string literals

If you use the N prefix and the string is <= 4,000 characters long it will be typed as nvarchar(n) where n is the length of the string. So N'Foo' will be treated as nvarchar(3) for example. If the string is longer than 4,000 characters it will be treated as nvarchar(max)

If you don't use the N prefix and the string is <= 8,000 characters long it will be typed as varchar(n) where n is the length of the string. If longer as varchar(max)

For both of the above if the length of the string is zero then n is set to 1.

Newer syntax elements.

1. The CONCAT function doesn't help here

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


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

The above returns 8000 for both methods of concatenation.

2. Be careful with +=

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

Returns

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

Note that @A encountered truncation.

How to resolve the problem you are experiencing.

You are getting truncation either because you are concatenating two non max datatypes together or because you are concatenating a varchar(4001 - 8000) string to an nvarchar typed string (even nvarchar(max)).

To avoid the second issue simply make sure that all string literals (or at least those with lengths in the 4001 - 8000 range) are prefaced with N.

To avoid the first issue change the assignment from

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

To

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

so that an NVARCHAR(MAX) is involved in the concatenation from the beginning (as the result of each concatenation will also be NVARCHAR(MAX) this will propagate)

Avoiding truncation when viewing

Make sure you have "results to grid" mode selected then you can use

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

The SSMS options allow you to set unlimited length for XML results. The processing-instruction bit avoids issues with characters such as < showing up as &lt;.

小开

Okay, so if later on down the line the issue is that you have a query that's greater than the allowable size (which may happen if it keeps growing) you're going to have to break it into chunks and execute the string values. So, let's say you have a stored procedure like the following:

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
小开

You mus use nvarchar text too. that's mean you have to simply had a "N" before your massive string and that's it! no limitation anymore

DELARE @SQL NVARCHAR(MAX);
SET @SQL = N'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO
小开 
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
小开

The accepted answer helped me but I got tripped up while doing concatenation of varchars involving case statements. I know the OP's question does not involve case statements but I thought this would be helpful to post here for others like me who ended up here while struggling to build long dynamic SQL statements involving case statements.

When using case statements with string concatenation the rules mentioned in the accepted answer apply to each section of the case statement independently.

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)

提交答案