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.
No comments:
Post a Comment