Limitations of SQL NVARCHAR and VARCHAR

Question

That's all, I have a big (inevitable) dynamic SQL query. Due to the number of fields in the selection criteria, a line containing dynamic SQL grows more than 4000 characters. Now I understand that for NVARCHAR(MAX) there is 4000 max, but looking at the executed SQL in Server Profiler for the statement

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

It seems to work (!?), For another big query, it causes an error related to this 4000 limit (!?), It basically truncates all the SQL after that 4000 limit and leaves me with a syntax error. Despite this, in the profiler, this dynamic SQL query shows the full (!?).

What exactly is going on here and should I just convert this @SQL variable to VARCHAR and continue with it?

Thank you for your time.

Ps. It would be nice to be able to print more than 4000 characters to view these large requests. The following restrictions are limited to 4000

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

is there any other cool way?

Answer 1

I understand that for NVARCHAR(MAX)

there are 4000 maximum values

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

From nchar and nvarchar to online grammar books

 nvarchar [ ( n | max ) ] 

Symbol | means these are alternatives. that is, you specify either n or the max literal.

If you decide to specify a specific n , then it should be from 1 to 4000, but using max defines it as a large data type of an object ( ntext replacement, which is deprecated).

In fact, in SQL Server 2008 it seems that for a variable, the 2 GB limit can be unlimitedly exceeded by enough space in tempdb ( shown here )

Regarding other parts of your question

Concatenation truncation depends on the data type.

• varchar(n) + varchar(n) will be truncated with 8000 characters.
• nvarchar(n) + nvarchar(n) will be truncated with 4000 characters.
• varchar(n) + nvarchar(n) will be truncated with 4000 characters. nvarchar has a higher priority, so the result is nvarchar(4,000)
• [n]varchar(max) + [n]varchar(max) will not truncate (for <2GB).
• varchar(max) + varchar(n) will not truncate (for <2GB), and the result will be printed as varchar(max) .
• varchar(max) + nvarchar(n) will not truncate (for <2GB), and the result will be printed as NVARCHAR(MAX) .
• NVARCHAR(MAX) + varchar(n) first convert the input varchar(n) to nvarchar(n) , and then perform the concatenation. If the varchar(n) string is longer than 4000 characters, then the cast will be nvarchar(4000) and truncation will occur .

String literal data types

If you use the prefix n and the string is <= 4000 characters, it will be typed as nvarchar(n) , where n is the length of the string. Thus, N'Foo' will be considered as nvarchar(3) , for example. If a string is longer than 4000 characters, it will be treated as NVARCHAR(MAX)

If you do not use the prefix n , and the string is <= 8000 characters, it will be typed as varchar(n) , where n is the length of the string. If longer varchar(max)

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

New syntax elements.

1. The CONCAT function does not help here

 DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000); SELECT DATALENGTH(@A5000 + @A5000), DATALENGTH(CONCAT(@A5000,@A5000)); 

The above returns 8000 for both concatenation methods.

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 

Please note that @A met truncation.

How to solve the problem you are facing.

You get truncated either because you combine the two data types of type max , or because you combine the varchar(4001 - 8000) string varchar(4001 - 8000) with the nvarchar string you entered (even NVARCHAR(MAX) ).

To avoid the second problem, just make sure that all string literals (or at least those that have lengths in the range 4001-8000) are prefixed with n .

To avoid the first problem, change the assignment from

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

For

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

so a NVARCHAR(MAX) participates in the concatenation from the very beginning (since the result of each concatenation will also be NVARCHAR(MAX) , this will spread)

Avoiding truncation when viewing

Make sure the "results to grid" mode is selected, and you can use

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

SSMS parameters allow you to set unlimited length for XML results. The processing-instruction bit avoids problems with characters such as < , displayed as < .

Answer 2

Well, therefore, if later in the line , the problem is that you have a request that is larger than the allowed size (which can happen if it continues to grow), you will have to break it into pieces and execute string values. So, let's say you have a stored procedure, for example:

 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 

Answer 3

You also use nvarchar text. that means you just have to have an “N” in front of your massive string and that! no more restrictions

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

Answer 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 

Answer 5

The accepted answer helped me, but I was confused when I did varchars concatenation involving case statements. I know that the OP question does not include case statements, but I thought it would be useful to post here for people like me who ended up here trying to create long dynamic SQL statements that include case statements.

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

 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)