278

I have declared a column of type NVARCHAR(MAX) in SQL Server 2008, what would be its exact maximum characters having the MAX as the length?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jerameel Resco
  • 3,409
  • 7
  • 23
  • 30

4 Answers4

582

The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage.

Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters.

Leo Tolstoj's War and Peace is a 1'440 page book, containing about 600'000 words - so that might be 6 million characters - well rounded up. So you could stick about 166 copies of the entire War and Peace book into each NVARCHAR(MAX) column.

Is that enough space for your needs? :-)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 35
    Just a nit pick. NVARCHAR uses 2 bytes for most characters. For unicode caracters over U+00FFFF. It'll use four bytes. – Eli Algranti Jul 28 '15 at 05:15
  • 3
    Nitpicking: "The storage size is two times n bytes + 2 bytes." ([Source](https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15#arguments)) – RobIII Jun 25 '21 at 13:02
  • 3
    "A common misconception is to think that NCHAR(n) and NVARCHAR(n), the n defines the number of characters. But in NCHAR(n) and NVARCHAR(n) the n defines the string length in **byte-pairs**" ([Source])(https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15#remarks) – RobIII Jun 25 '21 at 13:10
84

By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored.

If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice.

Source: https://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/d5e0c6e5-8e44-4ad5-9591-20dc0ac7a870/

Kols
  • 3,641
  • 2
  • 34
  • 42
Madushan
  • 6,977
  • 31
  • 79
14

From MSDN Documentation

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes

Community
  • 1
  • 1
JiNish
  • 193
  • 7
11

I think actually nvarchar(MAX) can store approximately 1070000000 chars.

Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
Francisco Javier Larios Soto
  • 127
  • 1
  • 2
  • Can you explain where you're getting that number, and what you mean by "accurately"? (i.e. are you saying that's not a hard limit, but with more characters there can be problems? What kinds of problems? Why?) – Adi Inbar Nov 26 '14 at 23:22
  • 7
    I am not sure, but I think, if nvarchar uses double space than varchar to store a char , and the maximun number of memory that can be used by nvarchar(MAX) to store chars is 2^31-1 => 2147483647, then 2147483647/2 is 1073741824. by accurately I mean that is not an exact number. – Francisco Javier Larios Soto Nov 27 '14 at 16:50