nvarchar(4001)?

MSDN has this to say on the subject:

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. ncan be a value from 1 through 4,000. max i开发者_开发问答ndicates 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.

This leaves me confused. I can define a column as being 1 - 4000 long, or 2147483647 long but nothing inbetween? Is my understanding correct? Why can't I be explicit about values inbetween?

NVARCHAR(MAX) covers everything else (not just 2 billion characters). If you need more than 4,000 characters the data is most certainly going to be off-page, so as far as behavior is concerned it doesn't matter if you've used 4,001 characters, 10,000 characters, or 10,000,000 characters. It only occupies the space you need, so don't think that you are wasting (2 billion characters - the length of your actual string).

Max will accept values between 4001 and 1073741823 (bear in mind storage size is approx 2x the length of the actual string).

The restriction is basically that anything over 4000 characters must be a MAX.

Because 4000 characters or less has one behavior in terms of storage and MAX has another behavior in terms of storage. And you really don't want to start forcing string length calculations on things that are 1M characters long do you? My current understanding is that up to 4000 characters is stored in-table and MAX is stored out-of-table.

Also NVARCHAR(MAX) and VARCHAR(MAX) are replacements for text and ntext.