nvarchar(max) or nvarchar(2048)

Question

Dear All,

I just would like to know, how is the performance difference  (in term of speed and also storage space) between the two.

I am sure some one would have asked the before, but I have been searching around the web but couldn't find my answer.

Thanks ain advance.

Answer 1

Please find the extract from the BOL:

================
nvarchar [ ( n | max ) ] Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.
================

Which shows that performance wise nvarchar(2048) is better than nvarchar(max) but storage wise nvarchar(max) will be able to store larger value.

HTH

Answer 2

It never mentioned about the storage on disk, neither do the performance.

Say for exxmple. I have a list of string (10000 of them) which are approximately 1000 to 2000 characters in length.

What will be the speed and storage space on disk difference if I store them in nvarchar(2048) and nvarchar(max)

Answer 3

This is a good question - and it will depend on your data distribution.

SQL Server will try to store the nvarchar(max) in row, and only store it off-row if it grows too large to be contained in the row (8060 bytes).  nvarchar(2048) will take up to 4096 bytes in the row, and will be stored in row, unless overflow occurs (ie, the entire row ends up larger than 8060 bytes, due to many large columns.

Row-overflow is almost bad for performance, so you'd want to minimise the chances of it happening.  The only time I can see a performance difference between the two is the situation where the nvarchar column is almost full for every row (~2000 characters), and you've specified that large text objects are to be always stored off-row (see http://msdn.microsoft.com/en-us/library/ms189087(SQL.90).aspx), and the majority of your queries don't touch this column.  This situation means that more rows will fit on the pages of your table, and only occasionally will you need to jump out-of-row to fetch the large object.

If you have the situation where the majority of your rows won't use the 2048 character limit, (e.g., the average is 100 characters), then it wouldn't matter if you chose nvarchar(max), or nvarchar(2048).  Either will be stored in-row unless it gets too large, but the benefit of nvarchar(max) is that you won't have a hard limit on the off-chance that your user wants to enter 3000 characters.

Answer 4

Thanks a lot :)