110

I'm new to the concept nvarchar(MAX). How many characters will it hold?

Scott Weldon
  • 9,673
  • 6
  • 48
  • 67
quakkels
  • 11,676
  • 24
  • 92
  • 149

3 Answers3

172

Max. capacity is 2 gigabytes of space - so you're looking at just over 1 billion 2-byte characters that will fit into a NVARCHAR(MAX) field.

Using the other answer's more detailed numbers, you should be able to store

(2 ^ 31 - 1 - 2) / 2 = 1'073'741'822 double-byte characters

1 billion, 73 million, 741 thousand and 822 characters to be precise

in your NVARCHAR(MAX) column (unfortunately, that last half character is wasted...)

Update: as @MartinMulder pointed out: any variable length character column also has a 2 byte overhead for storing the actual length - so I needed to subtract two more bytes from the 2 ^ 31 - 1 length I had previously stipulated - thus you can store 1 Unicode character less than I had claimed before.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Wow... so i guess i don't really need to worry about setting a max size validation rule then. I had better be carefull about when I use the nvarchar(MAX) though. – quakkels Nov 24 '10 at 18:17
  • 20
    @quakkels: unless you plan to top Tolstoi' War and Peace (approx. 3.1 million characters) almost 350 times over - no, you won't have to worry about capacity :-) And since it's a VARCHAR/NVARCHAR type, it will always only use as much space as needed - no worries about wasted or unnecessarily reserved space either... – marc_s Nov 24 '10 at 18:20
  • 2
    really? That's cool. Just out of curiosit, in what case would you not want to use nvarchar(MAX)? – quakkels Nov 24 '10 at 18:39
  • 6
    @quakkels: use `VARCHAR(MAX)`, if you don't need the 2-byte support for Asian, Arab or Cyrillic languages. Use `(N)VARCHAR(x)` if you know a string will never be longer than x characters (don't use `NVARCHAR(MAX)` for a first name - use `NVARCHAR(50)` or whatever makes sense to you) – marc_s Nov 24 '10 at 19:09
  • Tes, @quakkels, I only use specific sizes on `[n]varchar` to help enforce business rules. I know the DB isn't the place for business logic, but it is a very convenient and practical place to enforce certain rules, especially when dealing with multiple data sources outside just one app. – ProfK Dec 25 '15 at 18:02
  • 1
    I just started my morning asking me the same question and both of your answers conjured a big smile on my face. +1. – Matthis Kohli Aug 16 '16 at 05:13
  • Should it not be `(2 ^ 31 - 3) / 2`? Since there are always 2 bytes extra needed for "administration"? See also the answer of Adriaan Stander. – Martin Mulder Sep 18 '18 at 14:26
  • @MartinMulder: possibly - but compared to 1 billion, 73 million characters, you're really splitting hairs here..... – marc_s Sep 18 '18 at 20:00
  • @marc_s: True... it is not really import to me. But the OP ask for "how many", and "a lot" will not cover it. So in your answer you state "to be precise", and you did a very "precise" calculation (with `-1`). And I point out it may be `-3`... to be even more precise. Bottom line: Or your answer is precise or it is an approximation. Lets be transparant about it. – Martin Mulder Sep 19 '18 at 15:54
11

From char and varchar (Transact-SQL)

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

Community
  • 1
  • 1
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
5

2^31-1 bytes. So, a little less than 2^31-1 characters for varchar(max) and half that for nvarchar(max).

nchar and nvarchar

Thomas
  • 63,911
  • 12
  • 95
  • 141