SQL Server: How to create a table that fills one 8 KB page?

Question

Is there a way to calculate the amount of data that fills one 8 KB page in SQL Server and create a table that fills one page entirely? This kind of table is presented in in this blog post.

Answer 1

A row is maximum 8060 bytes but there is row overhead included.

CREATE TABLE dbo.FullPage1 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL)
GO

If you change filler2 to char(50) it breaks with

Creating or altering table 'FullPage1' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The 8060 is over all columns. There is no column overhead except the NULL bitmap (see later)

CREATE TABLE dbo.FullPage3 (
    ID int NOT NULL,
    filler1 char(2683) NOT NULL,
    filler2 char(2683) NOT NULL,
    filler3 char(2683) NOT NULL)
GO

Here, data is 8053 bytes with overhead of 2 bytes record type) + 2 bytes NULL bitmap pointer + 3 bytes NULL bitmap.

Even though we have no NULLable columns, the NULL bitmap exists. When we add a NULLable column, we don't increase the row size.

CREATE TABLE dbo.FullPage3 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL,
    filler3 varchar(8000) NULL)
GO

This gives a warning of course

Warning: The table "FullPage3" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.

The NULL bitmap is "one bit per column". Not just NULLable columns. Uncomment filler6v and it fails with "... including 8 bytes of internal overhead ..."

CREATE TABLE dbo.FullPage4 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL,
    filler1v varchar(10) NULL,
    filler2v varchar(10) NULL,
    filler3v varchar(10) NULL,
    filler4v varchar(10) NULL,
    --filler6v varchar(10) NULL,
    filler5v varchar(10) NULL
    )
GO

For more info about on-disk structures, see Inside the Storage Engine: Anatomy of a record. Interestingly, where is the 4 byte row header? This appears excluded from the 8060 bytes.

Tested on SQL Server 2008 SP1 and SQL Server 2005 SP3