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!

Solution 1

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.

Solution 2

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.

Solution 3

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

Comments

[Victor]

Great! that's the kind of information I was looking for. Thanks

[Remus Rusanu]

+1. Unavailability of ONLINE operations is actually an important point: introducing a MAX column makes all ONLINE operations unavailable.

[NickG]

What do you mean by an ONLINE operation?

[SQLMenace]

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)

[Gabe]

Note that this limitation of having to build offline for varchar(max) is gone in SQL Server 2012+. link

[TravisWhidden]

Might be worth adding if the 'affect' is a good or bad, and maybe a link showing reference.

[Triynko]

"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]

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

[Daniel]

I found it here stackoverflow.com/a/45806109/2046795

[Caveman]

Still, I heard from a colleague that had a table that was joining 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.