A Row is stored on a Page which is 8192 Bytes, and this is broken down in two key areas when considering physical storage. Firstly on all pages there is a 96 Byte Header. In addition to this we also need to store the Row structure as well as its location in the remainder of the Page.
The location of a Row is stored at the end of the Page working backwards using 2 Bytes per Row. These 2 Byte pairs are generally called the Slot Array. I have seen a few articles/explanations of the Slot Array listing this as 36 Bytes which is incorrect, unless of course you are storing 18 Rows.
Now as the Page Header is always 96 Bytes, if you were to create a table and insert a Row into it; then the earliest point that it could be placed is immediately after the Page Header.
This location immediately after the Page Header would mean that the final 2 Bytes of the Page (Byte 8190 and Byte 8191) would be 60 00 which when reversed becomes 0x60 or 96 as an integer.
If another Row is added then Byte 8188 and Byte 8189 would be used to store the location of this second Row. The order of these 2 Byte pairs within the Slot Array is determined by how they are indexed, not necessarily how they are stored on the Page. That is to say a Row stored towards the front of the Page may or may not appear towards the beginning of the Slot Array which grows from the end of the Page towards the start.
Here is a representation of how a row is stored internally within SQL server using the standard structure.
|Status Byte A||1|
|Status Byte B||1|
|Null Bitmap Offset||2|
|Fixed Length Columns||X|
|Null Bitmap(At least 1 Byte)||1(+1 per 8 Columns)|
|Variable Length Column Count||2|
|Variable Length Offset Array||2 per column*|
|Uniqueifier||4 (if needed)|
|Variable Length Columns||X|
|Row Version Contains timestamp + pointer to original record||14 bytes|
|Slot Array||2 bytes per Record/Row/Slot|
Now you will see that the fixed length columns are stored towards the start of the structure and then the variable length columns. *I have highlighted that the variable length column array is 2 Bytes per column, however if a column is null then the entry in the Slot Array is not needed unless it has a non null column to its right.
[Location A][Location B][Location C] AAA,BBB,CCC
[Location A][Location B][Location C] AAA,NULL,CCC
[Location A][Location B]
[Location C] AAA,BBB,NULL
In the first 2 examples all 3 location pointers are needed, however the 3rd example does not require the 3rd location pointer. This raises the interesting point that column order does matter, and that you should keep the variable length columns that have a high probability to remain NULL after variable lengths columns that are not.
Smallest Row Size
The smallest row size using the above mentioned format would be for a CHAR(1)
+1 Status Byte A
+1 Status Byte B
+2 Null Bitmap Offset
+2 Column Count
+1 Null Bitmap (This is always stored regardless)
This would work out as 8 Bytes for the Row plus 2 Bytes per the Row location in the Slot Array. Now it is worth noting however that this is not the smallest Row size as internally this would be padded by 1 additional Byte. If you store a CHAR(2) then no padding will take place. The reason this happens is because of an optimization which is best explained as follows:
The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.
For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.
Marcel van der Holst – MSFT
So although the Row is 8 Bytes it is stored using 9 Bytes. The smallest optimally stored Row therefore is a fixed data type of 2 Bytes.
Most Rows per Page
On that note then if we say that we have a 9 Byte Row Structure + 2 Bytes for the Slot Array to store in the 8096 Bytes (8192-96 Page Header) we can work out the most Rows per Page as follows:
8096/11 = 736 Rows
This post builds on information gathered from research, but the factual knowledge was gathered from Paul Randall blog posts.