Why is nvarchar(max) size limited to 4000 characters

Question

Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype. And my query length is more than 4000 characters and it is showing error. Check this screenshot

how do I solve this problem.

Answer 1

Try by replacing NVarchar(5000) to NVarchar(4000) at declaration part.Maximum allowed size for NVarchar is 4000.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

then how do i execute my query if i replace the length. i just found out that my query length is 4912 characters.

Answer 3

Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype.

The "problem" is you don't use NVarchar(max), you use Nvarchar =>

DECLARE @SqlQuery AS NVarchar(max);

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

or use nvarchar(max)

From BOL:

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

Answer 5

Declare

@s nVarchar(4001)/*(Error)*/

--Declare @s nVarchar(4000)/* No Error*/

--Declare @s nVarchar(MAX)/*No Error*/

Set

@s =N'Select 1'

exec

sp_executesql @s

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 6

Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype. And my query length is more than 4000 characters and it is showing error. Check this screenshot

how do I solve this problem.

Hi Harsha !

DECLARE    @SQLQuery NVARCHAR(MAX)

Please try to define it with NVARCHAR(MAX) and it will run your query.

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Thanks, Hasham Niaz

Answer 7

When i try with nvarchar(max), im not getting any error but the characters are getting limited to 4000 even if set more than 4000 characters.

Answer 8

Maximum allowed size for NVarchar is 4000.

---

@Latheesh, then is there any other possible way to execute my dynamic query with is more than 4000 characters.

Answer 9

When i try with nvarchar(max), im not getting any error but the characters are getting limited to 4000 even if set more than 4000 characters.

Because fix strings are limited to 4000 chars. Split it into several string.

At all, why do you want to use such a large "dynamic" sql statement, IMHO it makes not really sense.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 10

Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype.

The "problem" is you don't use NVarchar(max), you use Nvarchar =>

DECLARE @SqlQuery AS NVarchar(max);

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

@Olaf Helper, you are a genius, its working. what is the difference when we use small case and capital case, i mean NVarchar(max) and Nvarchar (max). how come it is working when i use nvarchar(max) or Nvarchar(max) ?

i need this dynamic sql for ssrs reports, i will be passing the parameters dynamically, like multiple values for single parameter.

Answer 11

When i try with nvarchar(max), im not getting any error but the characters are getting limited to 4000 even if set more than 4000 characters.

How do you know that? Did you run SELECT len(@SQLQuery) to find out? Or are you lured by the default settings in SSMS? You can adjust the settings under Tools->Options->Results to Grid.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

Try this: am not getting any error

Declare @s nVarchar(MAX)/*No Error*/
Declare @s1 nvarchar(MAX)
Set @s1 = Replicate('a',5000)
Set @s ='Select '''+@s1 +''''
print Len(@s)
exec sp_executeSQL @s

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 13

@Erland Sommarskog yes i check the with the query SELECT len(@SQLQuery)  and it is showing 4000.

Answer 14

Try this: am not getting any error

Declare @s nVarchar(MAX)/*No Error*/
Declare @s1 nvarchar(MAX)
Set @s1 = Replicate('a',5000)
Set @s ='Select '''+@s1 +''''
print Len(@s)
exec sp_executeSQL @s

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

@Latheesh NK, it will never show any error. but with nVarchar(max) (capital V) the length is limited to 4K.

Answer 15

Absolutely, thats why i have provided both in my example. May be am not so specific.Anyway, nice to see you happy.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 16

Have you declared @SQLQuery as nvarchar(Max)?

you can try this too

Declare @s Varchar(MAX)
Set @s ='Select '''+Replicate('a',5000) +''''
print Len(@s)
exec (@s)

-- This doesn't show any error

Regards
Satheesh

Answer 17

Try this: am not getting any error

Declare @s nVarchar(MAX)/*No Error*/
Declare @s1 nvarchar(MAX)
Set @s1 = Replicate('a',5000)
Set @s ='Select '''+@s1 +''''
print Len(@s)
exec sp_executeSQL @s

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

@Latheesh NK, it will never show any error. but with nVarchar(max) (capital V) the length is limited to 4K.

Are you saying that when you run code below you get result of 4000 and 5009? Are you serious?

Declare @s nVarchar(MAX)
Declare @s1 nvarchar(MAX)
Set @s1 = Replicate('a',5000)
Set @s ='Select '''+@s1 +''''
print Len(@s)
print LEN(@s1)

---

everything is a matter of probability...

Answer 18

Declare @s nVarchar(MAX)/*No Error*/
Declare @s1 nvarchar(MAX)
Set @s1 = Replicate('a',5000)
Set @s ='Select '''+@s1 +''''
print Len(@s)--5009
print Len(@s1)--5000
exec sp_executeSQL @s

I guess the miss is between my second thread and last thread. The above is the result set. Replicate('a',5000) always retruns 5000.

I ompared between my two threads not the two declarations in the last thread. Sorry for the mislead.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 19

To answer your question in the subject. Nvarchar is limited to 4000 characters because nvarchar(4000) and smaller is stored either in the same data page as the row or in an overflow page. nvarchar(max) can however also be stored in LOB pages. Data pages are 8192 bytes in size, some of these bytes are reserved for headers fields which leaves about 8000 bytes for data. Nvarchar uses 2 bytes per character so you can store a maximum of 4000 characters per page. Varchar uses 1 byte per character and therefor you can store a maximum of 8000 characters per page. In varchar, however, the different kinds of characters you can use is limited to the collation used, so if your don't need special characters you can also use varchar(5000) instead of nvarchar(max). For a local variable to store a dynamic query using nvarchar(max) is fine, though.

Answer 20

its not working people, i really don't get it. i have wasted enough time on this.

here is my query, it is inside a stored procedure

Declare @SQLQuery nvarchar(max) 
 
set @SQLQuery = 'SELECT 
......
.....
.....
AND bd.billingdate >= ''' + CONVERT(nvarchar(10), @pBillingStartDate, 120) + '''
....
...
...
...'
 
set @SQLQuery = @SQLQuery + ' ORDER BY 
Pg.[ProgramName] 
,Inv.VLANumber /* Agreement Number */
,So.UsageStartDate  '
 
print '-----------'
print @SQLQuery
print '-----------'
print 'query length'
print len(@SQLQuery)
Execute sp_Executesql @SQLQuery    

and when i execute this procedure, this is the output.

-----------
SELECT 
IIF(ISNULL(Pg.[ProgramName],'') = '','Unknown', Pg.[ProgramName]) AS 'Offering Name'	
..
..
..
..
..
INNER JOIN [dbo].[SalesOrderAgreementParticipant] Par  (NOLOCK) ON Par.[SalesOrderId] = SO.[SalesO
-----------
query length
4000
Msg 105, Level 15, State 1, Line 73
Unclosed quotation mark after the character string 'SalesO'.
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near 'SalesO'.	

what am i missing here... ?

Answer 21

Hi Hrasha,

Can you cast all parameter as nvarchar(Max).

Eg: CONVERT(nvarchar(MAX), @pBillingStartDate, 120)

Check the len printed in the below example for understanding:

Declare @s nvarchar(MAX)
Declare @s1 nvarchar(MAX)
Declare @d nvarchar(MAX)
Set @d = 'ee'
Set @s1 = Replicate('a',5000) + @d
Set @s =N'Select '''+@s1 + ''''
--Set @s = @s + '''22'''
print Len(@s)
print Len(@s1)
exec sp_executeSQL @s
/*Also try the below*/
Declare @s nvarchar(MAX)
Declare @s1 nvarchar(MAX)
Declare @d nvarchar(MAX)
Set @d = 'ee'
Set @s1 = Cast(Replicate('a',5000) as nvarchar(max)) + @d
Set @s =N'Select '''+@s1 + ''''
--Set @s = @s + '''22'''
print Len(@s)
print Len(@s1)
exec sp_executeSQL @s

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 22

Without seeing the full batch it gets difficult to diagnose.

However, I spot another serious shortcoming that you need to fix: This:

AND bd.billingdate >= ''' + CONVERT(nvarchar(10), @BillingStartDate, 120) + '''

Should be:

AND bd.billingdate >= @pBillingStartDate

And then the call to sp_executesql should look like this:

DECLARE @params nvarchar(MAX) = N'@BillingStartDate date'

Execute sp_Executesql @SQLQuery, @params,
        @BillingStartDate = @pBillingStartDate

And you should handle all parameter values that you interleave above the same way.

Parameterised SQL gives you three advantages:

1) Protection against SQL injection.
2) Better untilisation of the SQL Server cache, as queries with different parameters can share the same plan.
3) It is soooooo much easier to code.

Another issue that I spot is (NOLOCK). NOLOCK is a feature that requires very careful understanding to use in application code. The risks for incorrect results or spurious errors are considerable.

I suggest that you clean up your use of parameters first, and once you have done that, and it is still not working, post the full batch.

Then again, why are you using dynamic SQL in the first place? If you are dealing with dynamic search conditions there solutions which saves your from dynamic SQL entirely, see my article on
http://www.sommarskog.se/dyn-search-2008.html
This article also includes patterns for how to write dynamic SQL in a structured and organised way.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 23

Latheesh NK, when i change the datatypes to nvarchar(max) in all the convert functions, it is working.

Erland Sommarskog, i guess you are rite, i passed the parameters separately and it worked. coming to the NOLOCK, im not sure i can remove it or not. the query was previously written by someone and i modified that sql into a dynamic one.

I will be calling this procedure from SSRS, rdl file. So there I have option for multiple selection for single parameter, so in that case, i will get the values in comma separated state. For example for city, when multiple cities are selected, i will get

'New York','Delhi','London'

above cities will come to a single parameter, so i suppose dynamic sql is the only thing we can do here, am i correct. if there are any other alternatives, please suggest me.

thanks for the answers

Answer 24

No, you don't need to dynamic SQL to handle comma-separated lists! You wasted quite some precious time because of this misconeption!

I have other articles on my web site for handling lists of values. Either you unpack them to a table with a function, or even better you pass the values in a table-valued parameter. (But since I don't a single thing about Reporting Services, I can't help you on that side of things.)

Anyway, start here: http://www.sommarskog.se/arrays-in-sql.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]