I have come accross interesting puzzle yesterday on numbers 96 and 36.
Everyone knows that header size is 96 and maximum allowable row size is 8060.
But I was bit confused while I saw the response as 36 bytes is for row offset array (as this is not enough to point 40+ rows in a page).
So I decided to do quick R & D on this.
Purpose of this post to understand how space is managed within a page (for header, data rows and row offset array).
1. Page header size is 96 bytes - No control on this
2. 2 bytes for every row pointer in Row offset array
so 36 bytes of row offset array is not sufficient to point all rows/slots in a page.
Lets do quick POC on this.
Step1: Create a simple table that consume 9 bytes per record (+ 7 overhead bytes = 16 bytes) this is to do validation quicker
create table t1(intSlno int identity, strName char(5))
Step 2: Insert 500 records in this table
insert into t1 values('A')
Step 3: check number of pages consumed for this table
DBCC IND('<dbname>, 't1', -1)
Observation: 2 data pages are allocated for this.
Step 4: Lets look at number of records/slots stored in first data page (page header)
DBCC PAGE(<dbname>, 1, <pageid>, 1)
Number of Slots and free bytes in first page header
m_slotCnt = 428
m_freeCnt = 392
Bytes consumed for Page header: 96
Bytes consumed for 428 records: 428 * 16 bytes/record = 6848
Bytes consumed for row offset array: 428 records * 2 bytes/record = 856
Total (header + data + row offset array) : 96 + 6848 + 856 = 7800
Bytes free (as in page header): 392
Total bytes/Page: 8192
1. Bytes used for data in a page depends on number of records (columns, data type and column size)
2. Minimum bytes overhead/ Record: 7 bytes (with in record for only fixed length datatypes) + 2 bytes for row offset array to point a record = 9 bytes