Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't able to limit the field length at the database level)

like image 675
stucampbell Avatar asked Sep 29 '08 12:09

stucampbell


1 Answers

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

like image 52
David Kreps Avatar answered Oct 25 '22 12:10

David Kreps