CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
spender
spender

Reputation: 120528

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 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.

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?

Upvotes: 2

Views: 432

Answers (3)

JNK
JNK

Reputation: 65217

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.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280625

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).

Upvotes: 4

Orion Adrian
Orion Adrian

Reputation: 19573

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.

Upvotes: 1

Related Questions