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

This comes from an earlier thread on StackOverflow that can be found here:

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

The error is caused because you cannot have a row in SQL server which is larger than 8KB (the size of 1 page) because rows are not allowed to span pages - its a basic limit of SQL Server [...]

Note that SQL server will allow you to create the table, however if you try to actually insert any data which spans multiple pages then it will give the above error. Of course this doesn't quite add up, because if the above was the whole truth then single VARCHAR(8000) column would fill a row in a table! (This used to be the case). SQL Server 2005 got around this limitation by allowing certain data from a row to be stored in another page, and instead leaving a 24-bit pointer instead.

I would suggest normalizing your table into one or more relalted tables.

Answer 2

From the SQL Server documentation:

The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. Only their combined lengths can exceed the 8,060-byte row limit of a table.

The sum of other data type columns, including char and nchar data, must fall within the 8,060-byte row limit. Large object data is also exempt from the 8,060-byte row limit.

More info here: https://technet.microsoft.com/en-us/library/ms186981%28v=sql.105%29.aspx

Answer 3

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.