The Row Header (A Storage Wednesday Post)

Introduction

In February 2014, I started the Storage Wednesday series. Since then I have written every Wednesday about the way SQL Server stores and accesses data. The list of posts can be found at the end of the first one: The Page.

In continuation of the Storage Wednesday series, today I would like to invite you to discover the way a single row is organized within a data page. (The data page was introduced in The Row Offset Array.)

Row Header Example

To dive into the row header, we need a table with a few columns, some based on a fixed length data type and some based on a variable length data type. I am going to use this table structure:

This SQL batch (re-)creates the dbo.tst table and also inserts a few rows into it. The inserted strings in Col4 and Col5 have a random length, so you will see different lengths in every row.

Let us look at the first page. In my case the first page is page 1:282 and I am going to use DBCC PAGE with display type 3:

The output of these statements is too long to include here. However, for each row on the page you will find a section like this:

This output separates all the values for easier digestion. However, it does not cover the entire row header. The complete row data looks like this:

The row header is split into two pieces, separated from each other by the fixed length column values. The first two bytes, 0x3000 in our case, contain status bits. This value indicates the type of row and whether the row contains variable length data.

The next two bytes, 0x1400 in the example, is a pointer to the start of the second half of the header. Remember that al vaues are stored n reverse byte order, so this entry actually means 0x14 or 20. The remaining bytes of the first row in this output (01000000 f32daf6b 6d7579d1 073e380b) contain the values for the fixed length columns id, Col2 and Col3. The first two columns are of data type INT and take four bytes each, Col3 is a DATETIME2 column and uses eight bytes.

Starting with byte 0x14 we have two bytes that contain the number of columns in the row (0x0500). After that follows the NULL-bitmap. The NULL-bitmap contains one bit for each column in the row. That means the length of the NULL-bitmap is one byte for every eight columns in the row.

Following the NULL-bitmap, at position 0x17 in our example is the variable length section of the row. It is completely omitted if the row does not contain variable length columns. It starts with a two-byte field that contains the number of variable lengths columns (0x0200). It is followed by as many two-byte pointers, each pointing to the first byte after the respective variable length column. The variable length values follow right after those pointers, starting a position 0x1d in the example.

In total, there are six bytes in the header, plus the NULL-bitmap and plus the variable length column offset array that starts out with a two byte count. Every row in SQL Server 2012 has a NULL-bitmap (in some earlier versions that was optional), so the minimum header length is seven bytes if the row consists of less <=8[/tt] columns and no column is of a variable length data type.

Summary

Every row in SQL Server has its own header, the row header. It contains metadata describing the record and is at least seven bytes in length.