Performance of NVARCHAR(n) for large strings

What are the performance or storage implications of using a large value for NVARCHAR? For example, if I specify a NVARCHAR(4000) column just to cope with the rare case there are strings that long, but have a table full of strings 255 characters long, is the performance identical to specifying an NVARCHAR(255) column? Is there a reason then NOT to specify NVARCHAR(4000) on everything? i.e. does the query optimizer use it?

I know how long a row is and how many rows can fit in a page (4096 bytes) affects performance, but my understanding is that NVARCHAR only stores the characters needed so it wouldn't be affected unless there was actually a longer string. I wanted to know if there are any other considerations to using a large value here.

Also, how does NTEXT compare to using NVARCHAR? I noticed the documentation said it used a new page after 256 characters, which sounds like it's different from how a 4000 byte NVARCHAR would be stored?

Specifying nvarchar(4000) as opposed to nvarchar(256) when you only intend to store strings of max length 256 should not cause any significant perf hit.

On the other hand, nvarchar should be the preferable choice when compared to ntext for performance reasons. First of all, ntext (or image) cannot be indexed. Secondly, they are stored out of the row page and incur more overhead.

Performance of nvarchar(max)...

Hi-

I've noticed that I have gotten in the habit of using nvarchar(max) as the datatype for almost all of my string-type arguments when defining stored procedures. I'm curious if anybody has any thoughts on if this is a good or a bad practice?

So, is it better to say "create procedure foo(@.bar nvarchar(50) ..." or "create procedure foo(@.bar nvarchar(max) ..."?

Thanks,
-Eric.

It is recommended that you should use nvarchar(max) instead of nvarchar(n) when the size of column data might exceed 8000 bytes.

There is no difference in the behavior of varchar(n) and varchar(max). As a good practice, varchar(max) should be used as a substitute for text datatype and not of varchar(n).