What is the maximum number of characters that nvarchar(MAX) will hold?

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

nchar and nvarchar