I continue to read in many forums and on many blogs that a page is comprised as shown below:
Page Size: 16 x 512B = 8192B
Page Header: = 96B
Maximum In_Row Row: = 8060B
This leaves (8192 – 96 – 8060)B = 36B.
Ok, this is logical and correct. The question I have is this: why do so many people say that the remaining 36B is reserved for the slot array?
Obviously, the slot array gives 2B per row on the page; so, it can be as small as 2B and as large as 1472B:
2B: 1 row * 2B = 2B
1472B:
8096B = n*9B (min row size with overhead…think single TINYINT column) + n*2B (slot array cost per row)
=> 8096 = 11n
=> n = 8096 / 11 = 736.
736*2B = 1472B.
This gets me to 20 due to the 14B version tag.
USE master ;
GO
CREATE DATABASE test ;
GO
USE test ;
GO
ALTER DATABASE test
SET ALLOW_SNAPSHOT_ISOLATION ON ;
GO
ALTER DATABASE test
SET READ_COMMITTED_SNAPSHOT ON ;
GO
DROP TABLE tbl ;
GO
CREATE TABLE tbl
(
i CHAR(8000) DEFAULT(REPLICATE('a',8000))
, j CHAR(53) DEFAULT(REPLICATE('a',53))
) ;
INSERT INTO tbl
DEFAULT VALUES ;
GO
DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO
Another example. If you go to 50 from 49, you get the VARCHAR(MAX) going to LOB_DATA.
DROP TABLE tbl ;
GO
CREATE TABLE tbl
(
i VARCHAR(MAX) DEFAULT(REPLICATE('a',8000))
, j CHAR(49) DEFAULT(REPLICATE('a',49))
) ;
sp_tableoption N'tbl', 'large value types out of row', 'OFF' ;
GO
INSERT INTO tbl
DEFAULT VALUES ;
GO
DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO
It appears that this issue remains, even in SQL Server 2012. @SQLKiwi points to this post by Kimberly Tripp – http://www.sqlskills.com/blogs/kimberly/a-simple-start-table-creation-best-practices/.
Best Answer
Where pages are used for internal purposes like sort runs, the maximum row size is 8094 bytes. For data pages, the maximum in-row size including internal row overhead is 8060 bytes.
Internal row overhead can expand significantly if certain engine features are in use. For example, using sparse columns reduces the user-accessible data size to 8019 bytes.
The only example of external row overhead I know of up to SQL Server 2012 is the 14 bytes needed for versioned rows. This external overhead brings the maximum space usage for a single row to 8074 bytes, plus 2 bytes for the single slot array entry, making 8076 bytes total. This is still 20 bytes short of the 8096 limit (8192 page size - 96 byte fixed header).
The most likely explanation is that the original 8060 byte limit left 34 bytes for future expansion, of which 14 were used for the row-versioning implementation.