Max Row Size in SQL Server 2012 with varchar(max) fields

Question

I have created a table with column types as nvarchar(max), which my understanding is that they can support 2GB. However on inserting, I still receive this error:

Cannot create a row of size 8061 which is greater than the allowable maximum row size of 8060.

Is there a global setting on the database required, or is there another limit I am hitting? Is there a limit to the number of varchar(max) fields per table?

Answer 1

SQL server uses page to store data. Page size is 8kb.

So a record size (row size) in SQL server cannot be greater than 8060 bytes.

If data is not fitted in 8060 bytes then reference pointers are used. When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this limit, the SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page.

Moving large records to another page occurs dynamically as records are lengthened based on update operations. Update operations that shorten records may cause records to be moved back to the original page in the IN_ROW_DATA allocation unit.

Also, querying and performing other select operations, such as sorts or joins on large records that contain row-overflow data slows processing time, because these records are processed synchronously instead of asynchronously.

The record-size limit for tables that use sparse columns is 8,018 bytes. When the converted data plus existing record data exceeds 8,018 bytes, MSSQLSERVER ERROR 576 is returned. When columns are converted between sparse and nonsparse types, Database Engine keeps a copy of the current record data. This temporarily doubles the storage that is required for the record. .

To obtain information about tables or indexes that might contain row-overflow data, use the sys.dm_db_index_physical_stats dynamic management function.