Hello Dear Reader while learning about SQL Server the more you learn the more you start looking at SQL Internals and how data is stored on Disk. As you go deeper you will eventually use commands like DBCC IND to find Page numbers or DBCC PAGE to look at the contents of a data page to look at actual data records. When you do you will also see a numeric output along the left hand portion of your screen, it is the data as it is stored on the page, but it isn’t in a format that we would normally read it. I’ve been in presentations and read books written by some very smart people and I’ve heard them say that reading a data record takes a little practice. This isn’t so much something that you will use in your job as it is having some fun exploring the tool we use every day, so come along Dear Reader and let’s practice together.
We’ll start by looking at a graphical representation of a Data Record. This is the best I’ve ever seen and it comes directly from Paul Randal (@PaulRandal | Blog) and the MCM Video series on Data Structures.
When we look at a Data Record the first part of it is the Tag Bytes. Tag Bytes are two bytes and are made up of two parts Status Bits A and Status Bits B. Status Bits A are detailed in Kalen Delaney’s excellent book, SQL Server 2008 Internals.
Bit 0 contains versioning Information. Bits 1 to 3 are used as a 3 bit value to cover a host of things, (we’ll get to in a moment). Bit 4 indicates that a NULL Bitmap exists, there will always be a NULL Bitmap. Bit 5 is meant to show if a variable length field exists. Bit 6 will tell us if there is any versioning information in the record, and bit 7 is not used. Status B Bits are only used to indicate a ghost forwarded record. All of that information is stored in two bytes per data record, one byte for Status A and one byte for Status B.
So that was pretty technical, how do we relate this to English? First off let’s talk about how a record is displayed. On a page it will be displayed in Hexidecimal code, in order to read it we have to translate that to binary and then flip the binary. It’s clear as mud but let’s walk through it real quick.
First we’ll create a database and a table for this and insert a record.
Let's create a Clustered Index
Insert one data record
Now that we have our table and our data record, let’s take a closer look. I’ll be using DBCC IND and DBCC PAGE over and over again, the only thing that will change in future examples is the structure of the table we are creating.
Now we have our pages in our table, PageType 10 is an allocation unit our data page is a PageType 1, we can get our page number and plug it into DBCC PAGE. Don’t forget to turn on Trace Flag 3604 so we can get the output of DBCC PAGE to our SSMS window.
I’m only going to post the part that is relevant to us, so this is extremely paired down, and I’m only grabbing our first bit of data
0000000000000000: 10 00e405 01000000 736f6d65 2070726f 64756374 ..ä.....some product
In red you will find our Status A Tag Byte and in blue our Status B.
I hear what your saying Dear Reader, how is 10 all of that information I talked about up top, it doesn’t look like much. But this is actually 0x10 in hexadecimal, you can use the converter tool at http://easycalculation.com/hex-converter.php (plug in value 10) and watch as it is converted to binary. In binary it is translated to 00010000, we are not quite finished remember we need to flip this value to get what we are looking for. So how do we flip it, first we read it backwards one section at a time.
Now one important thing to note is that our 3 bits even though they say 0-7 just like everything else they are binary values as well 0=000, 1=001, 2=010, 3=011, 4=100, 5=101, 6=110, and 7=111.
3-bit binary value
So these tag tell us that this is a Primary Data Record, it contains no Versioning Information, no variable length fields, and no Row Versioning information.
So let’s take this same row in our table and delete it to make it a ghost record and see how our Tag bytes change.
We don’t want to commit or rollback the transaction just yet, we want the record marked as ghosted. Now let’s look at our column.
0000000000000000: 1c 00e405 01000000 736f6d65 2070726f 64756374 ..ä.....some product
Remember to convert your 1c value from Hexadecimal to binary, and flip your binary value.
Remember our chart 110=6, so this is indeed a Ghost Data Record. So now let’s drop our table and add a variable length column to it. Then we’ll insert a record.
Remember to use DBCC IND and DBCC PAGE to get our internal information, then let’s look at our record. And translate our Tag A bytes.
0000000000000000: 30 00e405 01000000 736f6d65 2070726f 64756374 0.ä.....some product
Here are our results.
And once again we see translated we have our Primary Data record with a null bitmap and variable length columns. One interesting note, if you alter your original table to have a variable length column you have to rebuild the table in order for the Tag bytes to change from 0x10 to 0x30.
Hope you enjoyed the read, I had fun writing this up.