SQL Nvarchar and Varchar Limits

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

Difference between Varchar(max) and nvarchar(max)

This is for Microsoft SQL Server:

NVARCHAR is Unicode - 2 bytes per character, therefore max. of 1 billion characters; will handle East Asian, Arabic, Hebrew, Cyrillic etc. characters just fine.

VARCHAR is non-Unicode - 1 byte per character, max. capacity is 2 billion characters, but limited to the character set you're SQL Server is using, basically - no support for those languages mentioned before

What is the maximum characters for the NVARCHAR(MAX)?

The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage.

Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters.

Leo Tolstoj's War and Peace is a 1'440 page book, containing about 600'000 words - so that might be 6 million characters - well rounded up. So you could stick about 166 copies of the entire War and Peace book into each NVARCHAR(MAX) column.

Is that enough space for your needs? :-)

cannot store 5000 chars in NVARCHAR and VARCHAR

Use the nvarchar(max) data type instead.
Be sure to prefix string literals with the N designator (N' ASDADASD...'). Cast explicitely so the default limitation will not apply:

set @script=convert(nvarchar(max),N'very-long-literal')

(reference: The weird length of varchar and nvarchar in T-SQL)

EDIT: full working example:

DECLARE @SCRIPT nvarchar(max)
SET @SCRIPT=convert(nvarchar(max),N' ASDADASD ASDA  DSADAD AD AS D......')+replicate(convert(nvarchar(max),N'A'),6000)
PRINT LEN(@SCRIPT) -- this prints "6036"
PRINT @SCRIPT -- this prints all 6036 chars of the string

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 limits to 8000 characters within stored procedure

Why not use sp_executesql?

EXEC sp_executesql @exec

As I can see @exec is nvarchar(max), you can pass it without a problem.

On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

EDIT

Use sp_executesql INSTEAD of OPENQUERY

DECLARE @paramDef nvarchar(max) = '@TaskQuery nvarchar(max)'

SELECT @exec = 'INSERT INTO [dbo].[Timesheet_Global_Info] EXEC '+QUOTENAME(@LinkedServer)+'.database.dbo.sp_executesql @TaskQuery',

EXEC sp_executesql @exec, @paramDef, @TaskQuery=@TaskQuery

Can i store more than 8000 character in a field of SQL table?

Kind of yes, but this gets messy rapidly, and in fact the work around would be worse performance than the varchar(max) option. The issue is the limit of 8060 bytes is permitted on-page for a single row. You can exceed that limit but as long as you accept the data being stored off-page and on a page elsewhere.

Preferred Option : use Varchar(Max) and allow LOB storage to be used.

Alternative : Use multiple varchar(8000) fields, and split / concatenate your string - the data will get Short Large Object'ed (SLOB) and the varchar(8000) fields will be stored off in different pages. This will make it less performant - not to mention the split / concatenate performance issues.

2nd Alternative - compress the data, but there is no guarentee you can still store it within the 8k limit.

Deprecated : Text - do not use this as a solution

In short - do not try to avoid varchar(max) - you would make your life a lot worse.

How much length can NVARCHAR(MAX) store?

An nvarchar(MAX) can store up to 2GB of characters. Each character in an nvarchar is 2bytes in size. 2GB is 2,000,000,000 bytes so an nvarchar(MAX) can store 2,000,000,000 / 2 characters = 1,000,000,000 characters.

So, to answer your question "Could you fit 20,000 characters into an nvarchar(MAX)?": Yes, you could (50,000 times).

Size limit of varchar(MAX) in SQL Server

All MAX datatypes--VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)--have a limit of 2 GB. There is nothing special you need to do. Without specifying MAX, the limit for VARCHAR and VARBINARY are 8000 and the limit for NVARCHAR is 4000 (due to NVARCHAR being double-byte). If you are not seeing any data come in at all, then something else is going on.

Are you sure that the column is even in the INSERT statement? If you submit test data of only 20 characters, does that get written? If you want to see what SQL is actually submitted by Linq, try running SQL Profiler and look at the SQL Statement: Statement Ended event, I believe.

Also, when you say that the "long string is empty", do you mean an actual empty string or do you mean NULL? If it is not NULL, you can also wrap the field in a LEN() function to see if there are blanks for returns at the beginning that push any non-whitespace characters out of view. Meaning, SELECT LEN(stringField), * FROM Table. Another thing to try is to use "Results to Text" instead of "Results to Grid" (this is a Query option).

EDIT:

Seeing that the field is marked as NOT NULL, are you sure that you are setting the ClientFileJS property of your object correctly? Is it possible that the empty string is due to that property being initialized as string ClientFileJS = ""; and is never updated?

---

Related Topics