Sql-server – SQL Server: Rows over 8KB: Where are the docs

sql serversql-server-2016

I need to properly understand if and how I can get 10KB or 20KB per row in SQL Server 2016, Standard Edition.

I am reading the SQL Server Tech specs:

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server#Engine

Specifically "Bytes per row"

It refers to something in BOL, that I cannot find.

Where is the section "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online. ?

Best Answer

  1. Table and Index Organization

Variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.

For every partition used by a table (heap or clustered table), index, or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page.

  1. Row-Overflow Data Exceeding 8 KB

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.

Another good answer here: Work around SQL Server maximum columns limit 1024 and 8kb record size

Related Question