SQL performance: Is there any performance hit using NVarchar(MAX) instead of NVarChar(200)

Question

I am wondering if there is any disadvantage on defining a column of type nvarchar(max) instead of giving it a (smaller) maximum size.

I read somewhere that if the column value has more than 4?KB the remaining data will be added to an "overflow" area, which is ok.

I'm creating a table where most of the time the text will be of a few lines, but I was wondering if there's any advantage in setting a lower limit and then adding a validation to avoid breaking that limit.

Is there any restriction on the creation of indexes with nvarchar(max) column, or anything that pays for having to add the restriction on the size limit?

Thanks!

score 50 · Accepted Answer · answered Dec 07 '10 at 19:11

Strictly speaking the MAX types will always be a bit slower than the non-MAX types, see Performance comparison of varchar(max) vs. varchar(N). But this difference is never visible in practice, where it just becomes noise in the overall performance driven by IO.

Your main concern should not be performance of MAX vs. non-MAX. You should be concerned with the question it will be possible that this column will have to store more than 8000 bytes? If the answer is yes, even by if is a very very unlikely yes, then the answer is obvious: use a MAX type, the pain to convert this column later to a MAX type is not worth the minor performance benefit of non-MAX types.

Other concerns (possibility to index that column, unavailability of ONLINE index operations for tables with MAX columns) were already addressed by Denis' answer.

BTW, the information about the columns over 4KB having remaining data in an overflow area is wrong. The correct information is in Table and Index Organization:

ROW_OVERFLOW_DATA Allocation Unit

For every partition used by a table (heap or clustered table), index, or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page.

So is not columns over 4KB, is rows that don't fit in the free space on the page, and is not the 'remaining', is the entire column.

"SQL 2012: You cannot do online index rebuilds at the partition level. You CAN, however, do online index rebuilds of the whole index of all data types except text, ntext, image. This means that tables with varchar(max), nvarchar(max), and varbinary(max) columns CAN BE REBUILT ONLINE in SQL 2012. SQL 2014: You CAN do online index rebuilds at the partition level. You CAN do online index rebuilds of varchar(max), nvarchar(max), and varbinary(max) data types. Just like with SQL 2012, text, ntext, image are not included. These are legacy data types which really should be phased out over time." — Triynko, Apr 14 '17 at 15:22
Still, I heard from a colleague that had a table that was `join`ing another table on an `id` `nvarchar(max)` field and got a 30% time decrease from changing it to a `nvarchar(100)`. Spamming `nvarchar(max)` is not ideal either. — Caveman, Jan 21 '22 at 10:34

score 21 · Answer 2 · answered Dec 07 '10 at 16:11

21

an index cannot be created on a column over 900 bytes. Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index

you can however use included columns

All data types are allowed except text, ntext, and image. The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types.

answered Dec 07 '10 at 16:11

SQLMenace

132,095
25
206
225

Great! that's the kind of information I was looking for. Thanks – willvv Dec 07 '10 at 16:14
1

+1. Unavailability of ONLINE operations is actually an important point: introducing a MAX column makes all ONLINE operations unavailable. – Remus Rusanu Dec 07 '10 at 19:12
1

What do you mean by an ONLINE operation? – NickG Aug 31 '12 at 14:37
There are two ways to rebuild an index OFFLINE and ONLINE, when you do it OFFLINE then the data will be unavailable for the duration of the rebuild, with ONLINE the data is available (except for a small time period when the index is created, a lock is being held) – SQLMenace Aug 31 '12 at 15:42
2

Note that this limitation of having to build offline for varchar(max) is gone in SQL Server 2012+. [link](https://msdn.microsoft.com/en-us/library/ms188388(v=sql.105).aspx) – Gabe Feb 11 '15 at 16:29
"SQL 2012: You cannot do online index rebuilds at the partition level. You CAN, however, do online index rebuilds of the whole index of all data types except text, ntext, image. This means that tables with varchar(max), nvarchar(max), and varbinary(max) columns can be rebuilt online in SQL 2012. SQL 2014: You CAN do online index rebuilds at the partition level. You CAN do online index rebuilds of varchar(max), nvarchar(max), and varbinary(max) data types. Just like with SQL 2012, text, ntext, image are not included. These are legacy data types which really should be phased out over time." – Triynko Apr 14 '17 at 15:22

score 5 · Answer 3 · answered Oct 09 '13 at 07:21

5

Choosing nvarchar(max) also can affect the execution plan optimizations that are adapted automatically by the sql server engine.

answered Oct 09 '13 at 07:21

Farshid

5,134
9
59
87

3

Might be worth adding if the 'affect' is a good or bad, and maybe a link showing reference. – TravisWhidden Dec 23 '16 at 00:52
I found it here https://stackoverflow.com/a/45806109/2046795 – Daniel Feb 26 '21 at 06:58

SQL performance: Is there any performance hit using NVarchar(MAX) instead of NVarChar(200)

3 Answers3

Linked