Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 9 down vote accepted

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

share|improve this answer
    
Great answer, thank you very much! –  jrara Jun 20 '11 at 8:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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