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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
A row is maximum 8060 bytes but there is row overhead included.
If you change filler2 to char(50) it breaks with
The 8060 is over all columns. There is no column overhead except the NULL bitmap (see later)
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.
This gives a warning of course
The NULL bitmap is "one bit per column". Not just NULLable columns. Uncomment filler6v and it fails with "... including 8 bytes of internal overhead ..."
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