For Nvarchar(Max) I am Only Getting 4000 Characters in Tsql

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

You have declared this as nvarchar(max) which allows 2GB of data so it will store 2GB.

What is happening:

• The datatype is not yet nvarchar(max) until assignment to @sql1
• Before that, it's a collection of strings, each less than 4000 (constants)
• You are concatenating short constants with short variables (short = < 4000)
• So you have 4000 characters put into @sql1

So, you have make sure you have nvarchar(max) on the right hand side.

One idea. The 2nd line concatenates nvarchar(max) with a constant = nvarchar(max)

SET @SQL1 = ''
SET @SQL1 = @SQL1 + 'SELECT DISTINCT Venue...
   ....

It's no different to the integer division that happens in every langauge.

declare @myvar float
set @myvar = 1/2 --gives zero because it's integer on the right

Operator precedence (infers datatype precedence) is always "assignment" last... why should unicode strings in SQL Server be any different?

SQL query variable nvarchar(max) can not store more than 4000 characters

You need to explicit cast your large string to NVARCHAR(MAX) like this:

DECLARE @count int, @sql nvarchar(max),@where nvarchar(max)
SELECT @count = 2, @where=' Where 1 = 1 '

set @sql = '
SELECT top 2
' + cast(@count as NVARCHAR) + CAST(' as [Count],
Name,
1 AS Column1,2 AS Column2,3 AS Column3,4 AS Column4,5 AS Column5,6 AS Column6,7 AS Column7,8 AS Column8,9 AS Column9,10 AS Column10,11 AS Column11,12 AS Column12,13 AS Column13,14 AS Column14,15 AS Column15,16 AS Column16,17 AS Column17,18 AS Column18,19 AS Column19,20 AS Column20,21 AS Column21,22 AS Column22,23 AS Column23,24 AS Column24,25 AS Column25,26 AS Column26,27 AS Column27,28 AS Column28,29 AS Column29,30 AS Column30,31 AS Column31,32 AS Column32,33 AS Column33,34 AS Column34,35 AS Column35,36 AS Column36,37 AS Column37,38 AS Column38,39 AS Column39,40 AS Column40,41 AS Column41,42 AS Column42,43 AS Column43,44 AS Column44,45 AS Column45,46 AS Column46,47 AS Column47,48 AS Column48,49 AS Column49,50 AS Column50,51 AS Column51,52 AS Column52,53 AS Column53,54 AS Column54,55 AS Column55,56 AS Column56,57 AS Column57,58 AS Column58,59 AS Column59,60 AS Column60,61 AS Column61,62 AS Column62,63 AS Column63,64 AS Column64,65 AS Column65,66 AS Column66,67 AS Column67,68 AS Column68,69 AS Column69,70 AS Column70,71 AS Column71,72 AS Column72,73 AS Column73,74 AS Column74,75 AS Column75,76 AS Column76,77 AS Column77,78 AS Column78,79 AS Column79,80 AS Column80,81 AS Column81,82 AS Column82,83 AS Column83,84 AS Column84,85 AS Column85,86 AS Column86,87 AS Column87,88 AS Column88,89 AS Column89,90 AS Column90,91 AS Column91,92 AS Column92,93 AS Column93,94 AS Column94,95 AS Column95,96 AS Column96,97 AS Column97,98 AS Column98,99 AS Column99,100 AS Column100,101 AS Column101,102 AS Column102,103 AS Column103,104 AS Column104,105 AS Column105,106 AS Column106,107 AS Column107,108 AS Column108,109 AS Column109,110 AS Column110,111 AS Column111,112 AS Column112,113 AS Column113,114 AS Column114,115 AS Column115,116 AS Column116,117 AS Column117,118 AS Column118,119 AS Column119,120 AS Column120,121 AS Column121,122 AS Column122,123 AS Column123,124 AS Column124,125 AS Column125,126 AS Column126,127 AS Column127,128 AS Column128,129 AS Column129,130 AS Column130,131 AS Column131,132 AS Column132,133 AS Column133,134 AS Column134,135 AS Column135,136 AS Column136,137 AS Column137,138 AS Column138,139 AS Column139,140 AS Column140,141 AS Column141,142 AS Column142,143 AS Column143,144 AS Column144,145 AS Column145,146 AS Column146,147 AS Column147,148 AS Column148,149 AS Column149,150 AS Column150,151 AS Column151,152 AS Column152,153 AS Column153,154 AS Column154,155 AS Column155,156 AS Column156,157 AS Column157,158 AS Column158,159 AS Column159,160 AS Column160,161 AS Column161,162 AS Column162,163 AS Column163,164 AS Column164,165 AS Column165,166 AS Column166,167 AS Column167,168 AS Column168,169 AS Column169,170 AS Column170,171 AS Column171,172 AS Column172,173 AS Column173,174 AS Column174,175 AS Column175,176 AS Column176,177 AS Column177,178 AS Column178,179 AS Column179,180 AS Column180,181 AS Column181,182 AS Column182,183 AS Column183,184 AS Column184,185 AS Column185,186 AS Column186,187 AS Column187,188 AS Column188,189 AS Column189,190 AS Column190,191 AS Column191,192 AS Column192,193 AS Column193,194 AS Column194,195 AS Column195,196 AS Column196,197 AS Column197,198 AS Column198,199 AS Column199,200 AS Column200,201 AS Column201,202 AS Column202,203 AS Column203,204 AS Column204,205 AS Column205,206 AS Column206,207 AS Column207,208 AS Column208,209 AS Column209,210 AS Column210,211 AS Column211,212 AS Column212,213 AS Column213,214 AS Column214,215 AS Column215,216 AS Column216,217 AS Column217,218 AS Column218,219 AS Column219,220 AS Column220,221 AS Column221,222 AS Column222,223 AS Column223,224 AS Column224,225 AS Column225,226 AS Column226,227 AS Column227,228 AS Column228,229 AS Column229,230 AS Column230,231 AS Column231,232 AS Column232,233 AS Column233,234 AS Column234,235 AS Column235,236 AS Column236,237 AS Column237,238 AS Column238,239 AS Column239,240 AS Column240,241 AS Column241,242 AS Column242,243 AS Column243,244 AS Column244,245 AS Column245,246 AS Column246,247 AS Column247,248 AS Column248,249 AS Column249,250 AS Column250,251 AS Column251,252 AS Column252,253 AS Column253,254 AS Column254,255 AS Column255,256 AS Column256,257 AS Column257,258 AS Column258,259 AS Column259,260 AS Column260
FROM Sys.Objects
 ' AS NVARCHAR(MAX)) + @where + ' AND  2 = 2 '


SELECT LEN(@sql),@sql

EXEC sp_executesql @sql

When you are concatenating nvarchar(1-4000) strings the output string is not converted to max if it is not possible to store all the data. Also, in your example, you are concatenating a nvarchar value with the second large string which is varchar(4251). You can test what is the output parameter type very easy using sp_describe_first_result_set.

So, we have:

EXEC sp_describe_first_result_set @tsql =   N'SELECT CAST(''a'' as nvarchar(50)) + cast(''b'' as varchar(4261)) as x'

and the result is a string as follows:

That's why we need to explicity convert the large string to NVARCHAR(MAX).

Nvarchar(max) shrinks to 4000 chars in SQL Server

You are not doing anything wrong. The Print command is limited to outputting 4000 characters (see BOL - Books Online, for more details). It does not mean nvarchar(max) has shrunk to 4000 characters.

Does nvarchar(max) only allow 4000 characters?

Sorry, just found out that this is a limitation of the message screen in Sql server management studio. The code below shows correct results

DECLARE @test NVARCHAR(MAX)
DECLARE @i INT
SET @i=0
set @test=cast('x' as nvarchar(max))

while(@i<6000)
begin
set @test= (@test+ cast('x' as nvarchar(max)))
set @i=@i+1

end

print len(@TEST)--6001

What is causing NVARCHAR(MAX) to be limited to 4000 characters using SQL Server 2019?

I believe the comments have answered the root cause (the length limit of a single message that can be returned via PRINT). Here is a solution that will return your data set split amongst multiple messages in the same batch.

Please note, this assumes that your IDs are comma separated and wont work without a delimiter.

DECLARE @IDs NVARCHAR(MAX);
DECLARE @remainder INT;

SET @IDs = 'Long delimited list here'

WHILE LEN(@IDs) > 0
BEGIN
    SET @remainder = CHARINDEX(','/*delimiter here*/,REVERSE(SUBSTRING(@IDs,0,4000)),0)
    PRINT SUBSTRING(@IDs,0,4000-@remainder)
    SET @IDs = SUBSTRING(@IDs,4000-@remainder/*+1 to remove the comma starting the new set*/,LEN(@IDs))
END

SQL NVARCHAR and VARCHAR Limits

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 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 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 <.

When is nvarchar(Max) = nvarchar(4000)?

A few points here, as I can't fit into a comment.

1. Yes. (n)varchar(MAX) was introduced in SQL Server 2005. Previously you had to make use of text, ntext and image for varchar(MAX), nvarchar(MAX) and varbinary(MAX). The old data type have been deprecated for a long time now and you should not be using them.
2. When combining data, data type precedence is used to work out the final data type. When lengths are involved, the combined values of the lengths are used (A varchar(10) and a varchar(100) concatenated would return a varchar(110). Note, however, that to achieve the usage of the MAX length, at least one string must be an (n)varchar(MAX). SELECT REPLICATE(N'A',3000) + REPLICATE(N'A',3000) AS S would return a 4000 character string. + (String Concatenation) (Transact-SQL) - Remarks:
   
   

   If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

3. Disable what functionality? The usage of (n)varchar(MAX)? Why? If you wanted to stop people using a data type stop them using (n)text and image. In all seriousness though, you can't stop the usage of a data type. Perhaps you could get "clever" with DDL triggers, but I advise against it.
   
   
   To answer the edit, sp_tableoption cannot be used to stop someone using a MAX length datatype no; my above point stands. To quote the documetation (sp_tableoption (Transact-SQL) - Arguments:
   
   

   Large value types out of row:
   
   1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.
   
   
   0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.
   
   
   Large user-defined type (UDT) applies to: SQL Server 2008 through SQL Server 2017.
   
   
   Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types.

4. Too broad for SO.

nvarchar(max) still being truncated

To see the dynamic SQL generated, change to text mode (_shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n

NVARCHAR(MAX) variable for building SQL Server 2008R2 dynamic query accepts only 4000 characters

First of all, since you have a NVARCHAR datatype, you should always use the N'...' format (with the leading N prefix) to clearly denote Unicode string literals.

DECLARE @WhereClause NVARCHAR(MAX) = N'WHERE 1 = 1'

Secondly, if that alone doesn't help, try casting the string literals to NVARCHAR(MAX) before concatenation

IF <condition>
   SET @SQL = @SQL + CAST(...... AS NVARCHAR(MAX))

---

Related Topics