0

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

4
  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

3

Extents, Pages, and Row overflow data pages are described in the product documentation:

Pages and Extents Architecture Guide

Large Row Support

Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8-KB). However, this does not include the data stored in the Text/Image page type.

This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.

  • This question asks specifically for the documentation link, which is out-of-date in the previous answer. – David Browne - Microsoft Nov 22 '18 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.