[N]VARCHAR(MAX) is a data type that was created to replace [N]TEXT.  Depending on how many characters you store in this data type, it will react differently to its surroundings. It might be just what you needed or it might be your worst nightmare, so choose wisely. Here’s a run down on this data type.

What is [N]VARCHAR(MAX)?

The [N]VARCHAR(MAX) data type was created to replace [N]TEXT. While NTEXT and TEST, and also IMAGE – not discussed here – are still available, they’re going to get dumped. Microsoft keeps threatening it, they just don’t say when!

How Does it Work?

VARCHAR can only be 8,000 Characters

Your standard VARCHAR data type can be defined as having a max length of 8,000. For NVARCHAR, the max length of 4,000 (because NVARCHARs can hold half the amount of characters than VARCHARs as it takes twice as much space to store them). Any time a VARCHAR is used, it will truncate anything over 8,000 characters.

VARCHAR(MAX) can hold up to 2GB

Now, this baby here *slaps the hood of the data type* is a suped up version of the [N]VARCHAR. VARCHAR(MAX) is not the same as saying VARCHAR(8000), nor is NVARCHAR(MAX) the same as saying NVARCHAR(4000). The MAX refers to the ability to store 2^31-1 bytes (or  2^30-1 characters for NVARCHAR), or 2GB. This data type can hold up to TWO GIGABYTES.

What’s it Going to Cost Me?

Some More Space

Now, you could define all your fields as a VARCHAR(MAX), but it’s not a good idea. Any VARCHAR(MAX) field with any data on it gets another 24 bytes attached, just for being MAX. That 24 bytes counts against your row max length total of 8,060 bytes. So, if you have a field that you expect to be 10 characters long and you make it a VARCHAR(MAX), it will be a total of 34 bytes. A VARCHAR(10) data type will still be 10 bytes. An NVARCHAR(MAX) field holding that same field will be 44 bytes, while an NVARCHAR(10) will only be 20 bytes. So, go easy on the MAX.

If you fill that VARCHAR(MAX) up enough, instead of the data being in the row, it’s going to be moved “out of row” – stored in it’s own virtual row with the original row storing a pointer.

Some More Time

When you insert a 2GB value into a VARCHAR(MAX) field, it’s going to take some time. It is going to fill up the datapage that your row is in, and then it’s going to have to just go with a pointer and put all that data into its own row and possibly into it’s own data page.

Specify the Data Type Every Time you Use It

Also, any time you are working with a VARCHAR(MAX), you need to specify that it is a VARCHAR(MAX), or else it’s going to be truncated to 8,000 characters. Notice in the REPLICATE statements used in the examples how the character to replicate is cast to a VARCHAR(MAX). If you don’t do that, it will only create an 8,000-length string.

The other cost is your usual functions aren’t going to work.

Otherwise, Your Usual Functions Might Not Work

Here’s an example. Notice that TestSmallMaxVal and TestMaxVal are both declared as VARCHAR(MAX). When I insert the records into the Test table, I am using REPLICATE so I don’t have two type out two million letter As. (I know, I know… no dedication to the craft.) For the TestSmallMaxVal I just say, “give me two million letter As.” For TestMaxVal, I say, “give me two million VARCHAR(MAX)-type letter As.”

CREATE TABLE dbo.Test
(
	TestVal			VARCHAR(10),
	TestSmallMaxVal		VARCHAR(MAX),
	TestMaxVal		VARCHAR(MAX)
);

INSERT INTO dbo.Test
(
	TestVal,
	TestSmallMaxVal,
	TestMaxVal
)
VALUES
(
	'A',
	REPLICATE('A',2000000000), 
	REPLICATE(CAST('A' AS NVARCHAR(MAX)),2000000000)
);

SELECT TestVal, 
	LEN(TestSmallMaxVal) testSmallMaxLen, 
	LEN(TestMaxVal) testMaxLen 
FROM dbo.Test;

Your results will be from the LEN selects will be as follows:

TestVal testSmallMaxLen testMaxLen
A 8000 2000000000

What happened with TestSmallMaxVal? It was treated as a VARCHAR, and VARCHARs can only be 8,000 characters in length. The rest is automatically truncated.

So…

Use VARCHAR(MAX) if you need to store 2GB, or at least more than 8,000 characters in a field. Otherwise, don’t. When you do use it, make sure you always CONVERT to VARCHAR(MAX) so you don’t get your VARCHARs truncated.

If this article was helpful, Like It or Share It! If you have a question, Ask It!

References

Read some more about [N]VARCHAR(MAX) in the following articles from Microsoft Docs linked below.

Tagged: