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

Quick recall:
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')
GO 500 

Step 3: check number of pages consumed for this table

DBCC TRACEON(3604)
DBCC IND('<dbname>, 't1', -1)
DBCC TRACEOFF(3604)

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 TRACEON(3604)
DBCC PAGE(<dbname>, 1, <pageid>, 1)
DBCC TRACEOFF(3604)

Number of Slots and  free bytes in first page header
m_slotCnt = 428
m_freeCnt = 392

Maths Now:

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

Lesson learnt:

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