SQL NVARCHAR and VARCHAR Limits
Solution 1
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.
-
varchar(n) + varchar(n)
will truncate at 8,000 characters. -
nvarchar(n) + nvarchar(n)
will truncate at 4,000 characters. -
varchar(n) + nvarchar(n)
will truncate at 4,000 characters.nvarchar
has higher precedence so the result isnvarchar(4,000)
-
[n]varchar(max)
+[n]varchar(max)
won't truncate (for < 2GB). -
varchar(max)
+varchar(n)
won't truncate (for < 2GB) and the result will be typed asvarchar(max)
. -
varchar(max)
+nvarchar(n)
won't truncate (for < 2GB) and the result will be typed asnvarchar(max)
. -
nvarchar(max)
+varchar(n)
will first convert thevarchar(n)
input tonvarchar(n)
and then do the concatenation. If the length of thevarchar(n)
string is greater than 4,000 characters the cast will be tonvarchar(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 <
.
Solution 2
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
Solution 3
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
Solution 4
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)
Related videos on Youtube
MoonKnight
Updated on April 10, 2020Comments
-
MoonKnight about 4 years
All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that there is a 4000 max set for
NVARCHAR(MAX)
, but looking at the executed SQL in Server Profiler for the statementDELARE @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 full(!?).
What exactly is happening here and should I just be converting this @SQL variable to VARCHAR and get on with it?
Thanks for your time.
Ps. It would also be nice to be able to print out more than 4000 chars to look at these big queries. The following are limited to 4000
SELECT CONVERT(XML, @SQL); PRINT(@SQL);
is there any other cool way?
-
Alex K. over 11 yearsMAX is not a synonym for the 4000 limit, its 1..4000 or MAX
-
HatSoft over 11 yearsWhy have you tagged the question with C# dll & setting swhen this is just a Sql Server question
-
MoonKnight over 11 yearsEdited. Thanks for spotting...
-
redcalx almost 11 yearsPRINT will concatenate at 4000 characters (for unicode) or 8000 chars (for single byte encodings). I suspect that is the source of the confusion here.
-
-
MoonKnight over 11 yearsThanks. There is a wealth of documentation and I have obviously got confused here. I am getting an error for this dynamic SQL query exactly on 4000 chars and there is nothing else I can find that is wrong with it... Thanks for your time.
-
Martin Smith over 11 years@Killercam - You might be getting an implicit cast to
nvarchar(4000)
along the way. If a string literal is less than 4,000 characters then it is treated asnvarchar(x)
. Concatenating to it anothernvarchar(x)
value will truncate rather than upcast tonvarchar(max)
-
MoonKnight over 11 yearsPerhaps. This error is a strange one - and is taking time to debug. Thanks again for your time... This clearly is the correct answer nad this was a foolish question - I am at the stage where I am doubting my beliefs!
-
MoonKnight over 11 yearsthis is the same as what I have i.e.
SELECT CONVERT(XML, @SQL);
. Thanks again... -
Martin Smith over 11 years@Killercam - It's different if your text contains any characters such as
< > &
.SELECT CONVERT(XML, '> <');
gives an error.select '> <' as [processing-instruction(x)] FOR XML PATH
works fine. -
MoonKnight over 11 yearsI have copied the statement to the question. I am at a loss, it is no doubt some stupid error but I can't see it for the life of me...
-
Martin Smith over 11 years@Killercam - You are probably getting truncation as per my first comment. Try changing the assignment to
DECLARE @SQL NVARCHAR(MAX) = ''; SET @SQL = @SQL +
so that anNVARCHAR(MAX)
is involved in the concatenation. -
MoonKnight over 11 yearsAdding
SET @SQL = N'...' + N'...';
has fixed this, I was not aware that you had to useN
prefixes for declaration strings. Again, thanks very much for your time... -
Martin Smith over 11 years@Killercam - Probably you have a string between 4,000 and 8,000 characters. With the
N
prefix that will be treated asnvarchar(max)
without it it will be treated asvarchar(n)
then implicitly cast tonvarchar(4000)
when you concatenate to annvarchar
-
Mudassir Hasan about 10 yearsi am enlightened by this answer
-
John Bell over 9 yearsAwesome answer. Thanks so much!
-
user3077222 almost 9 yearsreally helpful and doubt clearing answer.it helped me. thanks a lot
-
kate1138 almost 9 yearsI can't believe after a few years at T SQL I still meet varchar(max) truncate case that I can't explain. I hope this will be the last explanation I read up on this!!
-
MoonKnight almost 9 yearsThis is not the entire picture... 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 asnvarchar(3)
for example. If the string is longer than 4,000 characters it will be treated asnvarchar(max)
. If you don't use the N prefix and the string is <= 8,000 characters long it will be typed asvarchar(n)
where n is the length of the string. If longer asvarchar(max)
. For both of the above if the length of the string is zero then n is set to 1. -
Jeeva J over 8 yearsI had a table with varchar 200. Then later, I've modified to nvarchar(max). But When i insert the string, it allows only for 100 characters. What will be the issue?
-
jbd almost 8 yearsWhen debugging, keep in mind that PRINT will also truncate nvarhcar(max) at 4000 chars! That one cost me some time. Use DATALENGTH() (like Mr. Smith does above)