Coder Perfect

Limits for NVARCHAR and VARCHAR in SQL

Problem

I have a massive (unavoidable) dynamic SQL query for all of you. The string holding the dynamic SQL has grown to almost 4000 characters due to the quantity of fields in the selection criterion. Now, I understand that NVARCHAR(MAX) has a maximum value of 4000, however when I check at the run 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’s going on here, and should I just convert this @SQL value to VARCHAR and call it a day?

Thank you for your consideration.

P.S. To examine these large queries, it would be excellent to be able to print out more than 4000 characters. The following are limited to a total of 4000 characters.

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

Is there another cool way to do it?

Asked by MoonKnight

Solution #1

Your interpretation is incorrect. nvarchar(max) can hold up to 2GB of data (and sometimes more) (1 billion double byte characters).

The grammar is derived from nchar and nvarchar in online books.

nvarchar [ ( n | max ) ]

These are alternatives, as indicated by the | character. In other words, you can provide either n or the literal maximum.

If you supply a particular n, it must be between 1 and 4,000, but if you use max, it becomes a huge object datatype (replacement forntext which is deprecated).

In fact, it appears that in SQL Server 2008, the 2GB limit for a variable can be surpassed forever if there is enough space in tempdb (Shown here)

Regarding the remainder of your query,

If the string is longer than 4,000 characters and you use the N prefix, it will be typed as nvarchar(n), where n is the length of the string. For example, N’Foo’ will be considered as nvarchar(3). If the string exceeds 4,000 characters, it is converted to nvarchar (max)

If the string is longer than 8,000 characters and you don’t use the N prefix, it will be typed as varchar(n), where n is the length of the string. If you want to use varchar, make it longer (max)

If the length of the string is zero for either of the above, n is set to 1.

1. The CONCAT function doesn’t help h

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

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

For both concatenation procedures, the result is 8000.

2. Use += with caution.

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

It’s worth noting that @A was truncated.

You’re experiencing truncation because you’re concatenating two non-max datatypes or a varchar(4001 – 8000) string to a nvarchar typed string (even nvarchar(max)).

To prevent the second problem, simply prefix all string literals with N (or at least those with lengths between 4001 and 8000).

Change the assignment to avoid the first problem.

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

To

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

so that from the start, an NVARCHAR(MAX) is engaged in the concatenation (since the outcome of each concatenation will likewise be NVARCHAR(MAX))

Once you’ve selected “results to grid” mode, you can use it.

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

You can set an indefinite length for XML results using the SSMS parameters. The processing-instruction bit prevents characters like showing up as from causing problems.

Answered by Martin Smith

Solution #2

Okay, so if you have a query that is larger than the permissible size later on (which could happen if it continues to expand), you’ll have to break it up into chunks and run the string values. Let’s imagine you have a saved process that looks like this:

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

Answered by Mike Perrenoud

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

Answered by Max

Solution #4

The accepted answer was helpful, but I got confused up when executing varchar concatenation with case statements. Although the OP’s query does not include case statements, I thought it would be useful to publish this here for anyone who, like myself, have struggled to create long dynamic SQL statements that include case statements.

The rules described in the acceptable answer apply to each section of the case statement separately when utilizing case statements with string concatenation.

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)

Answered by Joe

Solution #5

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

Answered by Heta77

Post is based on https://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits