I'm new to the concept nvarchar(MAX). How many characters will it hold?
This question is related to
sql-server
tsql
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.
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.
2^31-1 bytes. So, a little less than 2^31-1 characters for varchar(max) and half that for nvarchar(max).
Source: Stackoverflow.com