SQL Server Nvarchar(max)

Question

I have an insert statement that one of the columns i have the column type nvarchar(max) desc. I have noticed that when I looked at some of the records in my database that desc column was sometimes blank. Being that there is validation through my GUI, I knew that was impossible. After further testing I found out that if the text for that column is to large, it just wont insert that data. I found this kind of odd b/c I thought it would give me some sort of error or not insert the record entirely, but it justs leaves that column blank. Is there a limit to the nvarchar(max) and if there is is there some other datatype i can use in its place. I did a test a the charcters I tried to insert is over 20,000 but it varies on end user inputs.

'**************EDITS****************************

Thanks everyone for you quick reply, through research I found that you guys are correct and it will hold (holding) that much information. What I found out is that when using Microsoft Management Studio if the size of the column your quering has to many characters when your results come back it appears blank. To verify this what I did was use the len(COLUMN_NAME) function and saw that the character length is what I expected. Does anyone know a way I can change the setings in Microsoft Management Studio to ACTUALLY see the results?

It would help if you can show how you are inserting the data from your code and what the data actually looks like. And what does "blank" mean: `NULL`, an empty string or something else? Others have mentioned the limits for `NVARCHAR(MAX)` (see the [documentation](http://msdn.microsoft.com/en-us/library/ms186939.aspx)) but there isn't enough information in your question to understand exactly what you're seeing. — Pondlife, Jan 31 '13 at 21:18

score 2 · Answer 1 · answered Jan 31 '13 at 19:13

2

There is a limit to NVARCHAR(MAX) - it is 2^31-1 bytes (2GB). You can read more about it at Books Online.

answered Jan 31 '13 at 19:13

Matt

2,982
1
22
23

score 1 · Answer 2 · edited May 23 '17 at 12:12

1

check the below links which might help alot.

The default size is 4000 and the max size is 2GB:

http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/d5e0c6e5-8e44-4ad5-9591-20dc0ac7a870/

What is the maximum number of characters that nvarchar(MAX) will hold?

edited May 23 '17 at 12:12

Community

1
1

answered Jan 31 '13 at 19:12

Alex

5,971
11
42
80

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

Please refer to this link

Quote:

Seriously - VARCHAR(MAX) can store up to 2 GB of data - not just 8000 characters.....

The point is: if you're using varchar(max), you need to make sure to always cast all your >strings to varchar(max) explicitly - as I did in this example. Otherwise, SQL Server will >fall back to "regular" varchar processing, and that's indeed limited to 8000 characters....

score 0 · Answer 4 · edited May 23 '17 at 12:18

varchar(max) should have no issues holding 20,000 characters so I think this might be related to something else.

I am not sure what tech your GUI is using but this thread seems to talk about a similar problem where provider you are using doesn't support varchar(max) and might require an update.

Strange problem with nvarchar(max) fields and Classic ASP

It might not be an issue with SQL server but the tech you are using to communicate to SQL Server.

Can you specify what tech you are using to communicate to SQL Server (ASP, ASP.NET, version?) and what version of SQL Server you are using as well.

SQL Server Nvarchar(max)

4 Answers4