Menu
I'm new to the concept nvarchar(MAX). How many characters will it hold?
988
Overview of SQL Server NVARCHAR data type In this syntax, max is the maximum storage size in bytes which is 2^31-1 bytes (2 GB). In general, the actual storage size in bytes of a NVARCHAR value is two times the number of characters entered plus 2 bytes.
With NVARCHAR(max), max specifies the maximum number of bytes that can be stored in the variable. The minimum size of the NVARCHAR value is 1 byte. The total length of an NVARCHAR variable cannot exceed 65,534 bytes. A variable declared as NVARCHAR without parameters has a maximum size of 1 byte.
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.
MsSql as of 2012 supports Ntext for example that allows you to go beyond 8000 characters in a variable. The way to solve this is to make multiple variables or multiple rows in a table that you can iterate through.
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.
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
