Are there any disadvantages to always using nvarchar(MAX)?

Same question was asked on MSDN Forums:

• Varchar(max) vs Varchar(255)

From the original post (much more information there):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

It's a fair question and he did state apart from the obvious…

Disadvantages could include:

Performance implications Query optimizer uses field size to determine most efficent exectution plan

"1. The space alloction in extends and pages of the database are flexible. Thus when adding information to the field using update, your database would have to create a pointer if the new data is longer than the previous inserted. This the database files would become fragmented = lower performance in almost everything, from index to delete, update and inserts. " http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

Integration implications - hard for other systems to know how to integrate with your database Unpredictable growth of data Possible security issues e.g. you could crash a system by taking up all disk space

There is good article here: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Based on the link provided in the accepted answer it appears that:

1. 100 characters stored in an nvarchar(MAX) field will be stored no different to 100 characters in an nvarchar(100) field - the data will be stored inline and you will not have the overhead of reading and writing data 'out of row'. So no worries there.

2. If the size is greater than 4000 the data would be stored 'out of row' automatically, which is what you would want. So no worries there either.

However...

3. You cannot create an index on an nvarchar(MAX) column. You can use full-text indexing, but you cannot create an index on the column to improve query performance. For me, this seals the deal...it is a definite disadvantage to always use nvarchar(MAX).

Conclusion:

If you want a kind of "universal string length" throughout your whole database, which can be indexed and which will not waste space and access time, then you could use nvarchar(4000).