292

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?

Improve this question
0

4 Answers 4

Reset to default
604

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? :-)

Improve this answer
3
  • 37
    Just a nit pick. NVARCHAR uses 2 bytes for most characters. For unicode caracters over U+00FFFF. It'll use four bytes.
    – Eli Algranti
    Commented Jul 28, 2015 at 5:15
  • 3
    Nitpicking: "The storage size is two times n bytes + 2 bytes." (Source)
    – RobIII
    Commented Jun 25, 2021 at 13:02
  • 4
    "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])(learn.microsoft.com/en-us/sql/t-sql/data-types/…)
    – RobIII
    Commented Jun 25, 2021 at 13:10
89

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/

Improve this answer
3
  • 13
    It is also NOT recomended if your data will never exceed 4000 characters as there are indexing issues.
    – HLGEM
    Commented Jun 21, 2012 at 13:46
  • I believe this to be outdated. See answer from @marc_s.
    – Fabio Milheiro
    Commented Jun 12, 2019 at 19:51
  • Hi @FabioMilheiro , can I ask which part is outdated. Is that the pointer storage part?
    – Xu Shaoyang
    Commented Feb 4, 2022 at 23:16
15

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

Improve this answer
14

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

Improve this answer
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
    Commented Nov 26, 2014 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
    Commented Nov 27, 2014 at 16:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.