SQL NVARCHAR and VARCHAR Limits
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 statement
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 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?
4 Solutions collect form web for “SQL NVARCHAR and VARCHAR Limits”
I understand that there is a 4000 max set for
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 ) ]
| character means these are alternatives. i.e. you specify either
n or the literal
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.
nvarcharhas higher precedence so the result is
[n]varchar(max)won’t truncate (for < 2GB).
varchar(n)won’t truncate (for < 2GB) and the result will be typed as
nvarchar(n)won’t truncate (for < 2GB) and the result will be typed as
varchar(n)will first convert the
nvarchar(n)and then do the concatenation. If the length of the
varchar(n)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
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
If you don’t use the
N prefix and the string is <= 8,000 characters long it will be typed as
n is the length of the string. If longer as
For both of the above if the length of the string is zero then
n is set to 1.
Newer syntax elements.
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);`
-------------------- -------------------- 8000 10000
@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
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
To avoid the first issue change the assignment from
DECLARE @SQL NVARCHAR(MAX); SET @SQL = 'Foo' + 'Bar' + ...;
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
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