I’m sure that most of us know that SQL Server stores all it’s data in 8Kb pages.  But what do these pages actually look like and how can we go about looking at them and picking them apart.

For this we’re going to need a couple of undocumented DBCC commands, DBCC page and DBCC ind.

DBCC ind ([database name or id],

DBCC ind will return all the pages that are associated with the specified index.  Let’s try running it for the people table in our SQLUndercover database and see what comes back…

dbcc ind ('SQLUndercover','people',1)

We can see the people table contains four pages and we’ve given a bunch of interesting information about each page.

From that data we can find the page IDs of all the data pages that are part of the clustered index.  We’re only really interested in looking at the data pages in this post (although I may go into the other page types in a future post) so let’s just pick one with a page type of 1.  1:264looks like a good bet.

You might sometimes see page references written in the format 1:264 or perhaps 12:1:264.  In the first example, the first number represents the file ID (1) and the second number, the page ID (264).  In the second example, the page reference has been prefixed with the database ID (12).

So how do we go about peering at that page?  Well my good people, that’s where our next undocumented DBCC command comes in, DBCC page.

DBCC page ([database], [file id], [page id], [print options])

DBCC page will take in a database name or id, file id and page id and return a representation of the specified page depending on the print options that you choose.

We’ve got four different print options that we can choose,

0 – Return only the page header

1 – Return the page header and hex dump of each row

2 – Return the page header and full page hex dump

3 – Return the page header, hex dump of each row as well as the details on each column

To instruct SQL Server to return the output of DBCC page to the console, we need to switch on trace flag 3604

So, to get the page 1:264 we’ll need to run the following code,

DBCC TRACEON (3604)
dbcc page('SQLUndercover',1,264,3)

Now lets take a look at the hex dump of the first row…

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c 

hmmmmm nice….. but what does it all mean???

Well the structure of a record follows a set format.

Dissecting The Record

Record metadata

Now that we know how a record is structured, we can start breaking it down and pulling information out of it.

First of all we know that the first two bytes represent the record’s metadata (0x3000)

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c 

We have to further break this metadata down into individual bytes if we want to understand what it’s telling us.

The first byte is bitmap with the following specification…

Page Type
0 – Primary Record

1 – Forwarded Record

2 – Forwarding Record

3 – Index Record

4 – BLOB Fragment

5 – Ghost Index Record

To read the metadata, we’re going to have to have a look at the byte in it’s binary form.

00110000

Bits 1-3 represents the page type, as we can see this is 0 so we know that we’re looking at a primary record.

Bit 5 indicates whether the record has a NULL bitmap

Bit 6 indicates whether the record has variable length columns

The second byte in the metadata will indicate if the record is a ghost forwarded record, a value of 1 will indicate a ghost forwarded record.

So from the above we now know that our record is a primary record with a NULL bitmap and contains variable length datatypes.

Pointer to NULL bitmap

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c 

The next two bytes are the pointer to the position of the NULL bitmap.  One thing that we need to remember is that this value is in little endian formatted hexadecimal (little endian means that the smaller byte is on the left as opposed to big endian where the smaller byte is on the right) and equates to 28 in decimal.  So we know that the NULL bit map will start at byte 28.

So now that we have that information, we can start to figure out what the data represents.  In order to do that, we’re going to need to know the table schema.  We can run the following code to get that…

SELECT cols.name, cols.column_id, DataTypes.name, DataTypes.user_type_id, cols.max_length
FROM sys.all_columns cols
JOIN sys.types DataTypes ON cols.user_type_id = DataTypes.user_type_id
WHERE OBJECT_ID('test') = cols.object_id
ORDER BY cols.column_id ASC

Fixed Length Data

The next section of the row contains the fixed length data.  We know that the NULL bitmap starts at byte 28, so that means that everything between that and the metadata is going to represent our fixed length datatypes.

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c 

Now lets look at that data schema again, there are two fixed length data fields, testID (int) and name (char).  The first fixed length column is testID, a 4 byte integer.

That means that the first 4 bytes in our fixed length data is going to be this integer (integers are represented in a similar way to the NULL bitmap pointer as little endian formatted hex value).

01000000 466c6176 69612020 20202020 20202020 20202020

That means that the value of testID is 0x00000001 or 1 in decimal.

The second fixed length column is ‘name’ with a length of 20 bytes.

01000000 466c6176 69612020 20202020 20202020 20202020

we can easily convert that into it’s native CHAR(20) to reveal it’s value

SELECT CAST(0x466c617669612020202020202020202020202020 AS CHAR(20))

“Flavia”

NULL Bitmap

The next bunch of values that we’ve got represent the NULL bitmap.  The NULL bitmap simply contain a bit for each column in the table indicating whether that column contains a NULL value.  A bit value of 1 represents a NULL value.

The first two bytes will tell us the number of columns that are represented by the NULL bit map.  The following, variable amount of bytes are the bitmap itself.  The bitmap will contain a single bit for every column that it’s tracking.

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c

As you can see, the first two bytes in the NULL bit map, the column count give us the hex value 0x0006 or 6 in decimal, this tells us that the NULL bitmap references six columns.  Knowing that, we now know that the bitmap is going to be a single byte in length (it’s only ever long enough to contain enough bits to cover the number of referenced columns, eg. if it was representing 12 columns it’d be 2 bytes in length).

The byte following the column count, the bitmap itself is 0xd0.  To really understand what that’s telling us, we’re going to need to look at it in it’s binary form.

11010000

Since we’ve only got six columns, we’re only really interested in the first six bits.  Any spare bits, those that don’t represent a column always have the value of 1.

11010000

The position of the bits correspond with the column number that they represent, so the first bit will represent column 1, the second bit will represent column 2 and so on.  So for our table, the bits would map out as follows…

So from the above, we now know that the country column in our record is NULL.

Number of Variable Length Columns

After the NULL bitmap, the next byte will give us the number of variable length columns in the row.

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c

As we know from the schema, there are 4 variable length columns

Offset Pointers to Variable Length Columns

The next group of bytes will provide us with a 2 byte offset for each of the variable length columns, this number points to the end of the column.  We have four variable length columns so we know that this section will be 8 bytes (2 bytes * no of variable length columns) long.

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c

Looking at the above, we can see that the offset (end of the column) for each of our variable length fields will be as below,

Street – 0x003c

City – 0x0040

Country – 0x0040

Words – 0x0075

That breaks down as follows…

30001c00 01000000 466c6176 69612020 20202020 
20202020 20202020 0600d004 003c0040 00400075 
00313836 2d343939 35204665 6c697320 526f6164 
47656e74 646f6c6f 72207175 616d2c20 656c656d 
656e7475 6d206174 2c206567 65737461 7320612c 
20736365 6c657269 73717565 20736564 2c

One interesting thing to note is that Country has the same off set as it’s preceding column, City.  This means that Country is either an empty string or it’s NULL, we know from the NULL bitmap that Country is actually null.

So now that we’re able to identify the values of each of the individual variable length columns it’s pretty simple to take those values and convert them into their native datatypes.

00313836 2d343939 35204665 6c697320 526f6164

SELECT CAST(0x003138362d343939352046656c697320526f6164 AS VARCHAR(255))

Street = 186-4995 Felis Road

47656e74

SELECT CAST(0x47656e74 AS VARCHAR(255))

City = Gent

646f6c6f 72207175 616d2c20 656c656d 656e7475 6d206174 2c206567 65737461 7320612c 20736365 6c657269 73717565 20736564 2c

SELECT CAST(0x646f6c6f72207175616d2c20656c656d656e74756d2061742c206567657374617320612c207363656c65726973717565207365642c AS VARCHAR(MAX))

Words = dolor quam, elementum at, egestas a, scelerisque sed,

And there it is, hopefully can you now understand the internal structure of a row of data in SQL Server.

Thanks for reading