Data Pages and the Row Offset Array

Introduction

Two weeks ago, I started the Storage Wednesday series. In this series, always on a Wednesday, I am going to write about the way SQL Server stores and accesses data. The list of posts can be found at the end of last week's post, The Page.

In How SQL Server stores Data I mentioned that everything in SQL Server is stored in pages. That includes metadata about the database itself as well as catalog data helping SQL Server to find the pages that belong to a specific object or to locate an empty page. However, most importantly it includes the actual table row data. Dependent of the type of data stored on a page, SQL Server assigns a numeric page type to it. Today I would like to invite you to take a look at Page Type 1: The Data Page. We are also going to look at the Row Offset Array, which is an integral part of any data page.

The Data Page

Independent of the table having a clustered index defined, or it being a heap, the actual row data is always stored in pages of type 1, called data pages. As all other pages too, a data page has a 96 byte page header.
In the header, information about the page is stored. You can for example find a reference to the table (and index) that the page belongs to in here. In addition, on a data page, one of the fields contains the number or rows stored on that page. For additional information on the page header check out last week's post about the page header.

All the way at the end of the page, you can find the row offset array or slot array. The slot array is a list of 2 byte pointers to the beginning of each row. The data belonging to a row together with its row header is called a slot. The slot array allows SQL Server to quickly find the beginning of each row. It also allows for the rows in a clustered index to be stored out of order within a page; the slot array determines the logical order (the index order) even if it is different from the physical order of the row on the page.

Row Offset Array Example

Let us look at that slot array in a little more detail:

In this image I have highlighted the three slot array entries as well as the first bytes of the slots they are pointing to. I did cut out a few lines of the output to make it fit on the screen. I also highlighted the slot count value in the header. Below the memory dump DBCC PAGE printed a decoded form on the slot array.

The first thing to note is that the address on the left is the memory address that the page got loaded into. Only the last four digits of this value are relevant to us here. Also, every value on a page is stored on the page in revers byte order. So a 0300 actually represents the hex value 0x0003. In addition, the slot array is also stored in reverse order. That means that the last two bytes of the page always point to the beginning of the first row on the page.

You might have also noticed that each of the three rows starts with the same byte pattern. That is the row header. We will talk about the row header next week.

Summary

In SQL Server, Page Type 1 is used to store the actual data. Each data page is organized in slots, each containing the data of a single row. The starting position of each slot is noted in the row offset array at the end of the page.