Benefits/drawbacks with NVarChar(max)

Hi,

I wonder if there are any drawbacks with NVarChar(max) contra e.g. NVarChar(40) when it comes to performance and amount of storage used?

Are there other arguments to use e.g. NVarChar(40) than that you have more control of how long the strings are when you set the upper limit?

I'm using Sql Server 2005.

Tomsi

Hey Tomsi. Using any of the (max) datatypes will basically tell the server that the data in that column could possibly grow to 2gb if desired. To achieve this, the database engine will evaluate the size of the data being inserted/updated into the column and store it appropriately depending on the size. If the size of the data being stored will fit in-row with the rest of the data for that row (i.e. if it's less than 8k minus the size of the other columns in the row give or take some other considerations), then SQL Server will store the data in-row with the rest of the row data. If it is larger than that and can't be stored in-row with the rest of the row's data, then SQL Server will store a pointer in the row with the data that points to the location of the actual data elsewhere. During read/write operations for that row from now on, the engine will have to jump from the pointer to the data, get/update the data, then jump back to the pointer, so read/write time is slowed in this case.

You'll also see a slight additional overhead for determining if the data can fit in row or not, but that will be miniscule.

General recommendation would be that if you know the size of the data will never exceed 'x', and x is <= ~8000 bytes, then use nvarchar(x)...if the data will in some cases or always exceed that size, then use the (max) indicator...

You could see this article for a bit more info and go from there

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

HTH