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