Yesterday, I received a question about the difference between nvarchar(n) and nvarchar(max).

• Why don’t we always use nvarchar(max)?

• Are nvarchar(n) and nvarchar(max) different in performance ?

• Are nvarchar(n) and nvarchar(max) different in storage size ?

First of all, What is nvarchar(n)?

nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

(From:  Microsoft Doc – nchar and nvarchar)

“n” in nvarchar(n) define the string length from 1 to 4,000. If your string is longer than 4,000 characters, you can define nvarchar(max) instead.

So, I want to store a string with 7 characters: “coderky”. What is the different between nvarchar(7) and nvarchar(4000) ?

The answers is: there is  no different between nvarchar(7) and nvarchar(4000)  in term of performance & storage size. 

But, do not use a large n. Just use a small enough. Because the actual size of your string will impact to your indexing in term of performance. and your string can’t be indexed if It’s greater than 900 bytes (450 Unicode characters)

There is an interesting thing is that: if you change nvarchar(7) or nvarchar(4000) to nvarchar(max). There is a difference in term of performance & storage size. 

Wow, Why is this happen? It’s because of the difference between the storage method in MS-SQL.

In MS-SQL Server all data rows are stored in Pages.

Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

From: Microsoft Doc – Understanding Pages and Extents

Your table is stored as a collection of 8-KB pages.

(From Microsoft Doc  – Table and Index Organization)

In the image above you can see that there are 3 types of allocation Units

• Data (IN_ROW_DATA) : Data or index rows that contain all data, except large object (LOB) data.
• LOB (LOB_DATA): Large object data stored in one or more of these data types: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), or CLR user-defined types (CLR UDT).
• Row overflow (ROW_OVERFLOW_DATA): Variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.

You can see that: If we use nvarchar(max) your data will be stored in LOB_DATA instead of IN_ROW_DATA or ROW_OVERFLOW_DATA. 

By default, all rows are stored in IN_ROW_DATA, which give you a great performance. But in some case, your row is larger than 8KB or your data is a LOB data (nvarchar(max)), then MS-SQL will split your columns into another area (LOB_DATA or ROW_OVERFLOW_DATA) and leave a pointer in IN_ROW_DATA to point to LOB_DATA/ROW_OVERFLOW_DATA. 

What is the reason why: nvarchar(max) will be slower than nvarchar(n)

About the storage size, there is a tiny different: extra bytes to a pointer. So you do not need to worry about the storage impact between nvarchar(n) and nvarchar(max).

Conclusion

Why don’t we always use nvarchar(max)  ?

• nvarchar(max) is slower than nvarchar(n).
• you can not index nvarchar(max) column

Are nvarchar(n) and nvarchar(max) different in performance ?

• Yes, nvarchar(max) will slower than nvarchar(n).

Are nvarchar(n) and nvarchar(max) different in storage size ?

• Yes, nvarchar(max) will add some extra bytes for pointers. But it’s not a big deal to worry about (in some case, if your row size is larger than 8KB the nvarchar(n) will add some extra byte for pointers as well)