SQL Server And XML: VARCHAR/NVARCHAR (N) vs (MAX)

Prior to SQL Server 2005, it was hard dealing with large string values. NVARCHAR and VARCHAR data types had a limitation of 8000 bytes (VARCHAR(8000) and NVARCHAR(4000)). To store large values, most people used TEXT columns and others broke the value into multiple rows. Dealing with TEXT columns was not easy. You cannot declare a variable of type TEXT. So reading information from a TEXT column was hard. Further, most of the string functions that we use regularly, do not support TEXT data type.

Life became easier with SQL Server 2005, when it introduced VARCHAR(MAX) and NVARCHAR(MAX) data types. VARCHAR(MAX) and NVARCHAR(MAX) can now store values up to 2 GB.

What does that mean? Can I declare a variable or column as VARCHAR(9000)?

The answer is NO. You can either declare a variable as VARCHAR(8000) or VARCHAR(MAX). But none in between. This leads to the question: "How do we restrict the length of the field then"? This can be achieved by adding a CHECK constraint.

-- let us create a table
CREATE TABLE Customers (
    CustomerID INT,
    -- Other fields,
    Notes VARCHAR(MAX))
GO
 
-- let us add the check constraint
ALTER TABLE Customers
ADD CONSTRAINT CustomerNoteLength
CHECK (DATALENGTH(Notes) <= 9000)
GO
 
-- expect an error
INSERT INTO Customers( CustomerID, Notes )
SELECT 1, REPLICATE( 'a', 9001 )
 
/*
TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): (1 row(s) affected)
!!!!!! NO ERROR !!!!!!!!
*/

Well, we expected an error. But the insert statement did not produce an error. Why? Did SQL Server make a mistake? Why did it accept a value which is longer than 9000 characters?

Let us check the length of the data we just stored:

SELECT LEN(Notes) FROM Customers
 
/*
--------------------
8000
*/

Oh...NO! Am I getting crazy?

The column has a restriction of 9000 characters. Our insert statement with 9001 characters successfully ran. The column has 8000 characters stored in it. All the statements conflict with each other.

Here is what happened. The REPLICATE function was expected to produce 9001 characters. But it returned only 8000 characters. All string functions that takes VARCHAR data type assumes the variable to be VARCHAR(8000) unless one of the parameters is VARCHAR(MAX). (in case of NVARCHAR, it is 4000). So, here is how we could generate a string of 9001 characters.

INSERT INTO Customers( CustomerID, Notes )
SELECT 3, REPLICATE( CAST('a' AS VARCHAR(MAX)), 9000 )
 
SELECT LEN(Notes) FROM Customers WHERE CustomerID = 3
/*
--------------------
9000
*/

Let us see if the CHECK constraint we created works or not.

INSERT INTO Customers( CustomerID, Notes )
SELECT 1, REPLICATE( CAST('a' AS VARCHAR(MAX)), 9001 )
/*
TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CustomerNoteLength". The conflict occurred in database "master", table "dbo.Customers", column 'Notes'.
TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian):
The statement has been terminated.
*/

Yes, it is working!

Storage of NVARCHAR(MAX)/VARCHAR (MAX) values

VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) and XML data types are called Large Value Types. SQL Server stores each record in a storage location called a 'data row'. The size of a row is 8060 bytes. When you store a Large Value Type to a column, if the total size of the row (including your Large Value Type) value is less than 8060 bytes, the value is stored "in row". That means the value is stored in the same data row where other values of the same record are stored. If the Large Value Type is larger, it is stored "out of row" which means that the data is stored in another location and a pointer to the stored location will be added in the data row. Reading or writing values "out of the row" will need some additional processing and hence is not as fast as "in row" operations.

VARCHAR(MAX)/NVARCHAR(MAX) columns are internally handled as TEXT columns. Though we can work with them as regular strings, under the covers there is some TEXT processing happening. Another important point to note about VARCHAR(MAX)/NVARCHAR(MAX) columns is that, you cannot create an index on those columns.