Have you ever looked at the first, root, and FirstIAM columns in sysindexes and wondered…

“What do these values mean?”

Recently, I took the time to dig into these values to figure out what they meant.  And then, how to take the information that they contain and turn it into something useful.

By the Book

To begin with, sysindexes is a documented compatibility view, so there is some information on these values within Books Online.  According to that source, the columns contain the following:

• First: Pointer to the first or root Index page.
• Root: Pointer to the root Index page
• FirstIAM: Not defined, but is actually the first Index Allocation Mapping (IAM) page.

The pointer value is stored as a binary(6) data type that actually contains two pieces of information.  The first is the page id for the data page this is being references and the second is the file id where the page exists.

Along with those details about the columns are a few other items about the columns, which don’t necessarily apply to this post.  For instance, there are some special conditions based on partitioning and whether the indexes is a heap, clustered, or non-clustered.

In this post, we’ll look at the columns first, root, and FirstIAM and turn them into something useful.  To start, each of these columns contains two pieces of information.  They each contain the file id and the page id for the page in the database that the column references.

Binary Unpacking

Now that we’ve defined the columns in the sections above, the next step is to unpack the values stored in the binary format.  Since there are two values, the key to extracting the values is to understand where they are stored and the how to reconstruct them into the page id and file id.

To start, as already mentioned, the values are stored in binary value with a length of 6.  That means that each of the values are represented in text by 2-characters, as shown in Figure 1.  The first four binary values are the page id and the last two values are the file id.

Figure 1 – Binary representation of database page

Of course, it can’t be quite this simple.  The values in the binary values are stored in reverse and need to be flipped before they can be converted to an INT.  Thus, the file id is comprised of values 5 and 6 and need to be rearranged to 6 and 5 before being converted to an int.  And for the page id, the values 1, 2, 3, and 4 are required and need to re-ordered into 4, 3, 2, then 1, after which they can be converted to an int.

To demonstrate retrieving the file id and page id from a binary value, execute the code in Listing 1.  This statement will return a file id of 1 and a page id of 4621.  With this information at hand, you can use your favorite DBCC command to dig in and uncover more information about the index.

--Listing 1 – Query for file id and page id
DECLARE @BinaryPage BINARY(6)
SET @BinaryPage = 0x0D1200000100
SELECT FileID = CAST(SUBSTRING(@BinaryPage,6,1)
+ SUBSTRING(@BinaryPage,5,1) AS INT)
,PageID = CAST(SUBSTRING(@BinaryPage,4,1)
+ SUBSTRING(@BinaryPage,3,1)
+ SUBSTRING(@BinaryPage,2,1)
+ SUBSTRING(@BinaryPage,1,1) AS INT)

Applying to sysindexes

The most useful way to use this logic, though, is not going to be through a one-time, one-value query.  Instead, the need will be to push it into a query for sysindexes and dig out the file id and page id values from there.  To do this, use the query in Listing 2 and you will be able to get the values split apart for the First, Root, and FirstIAM pages.

--Listing 2 – Query binary values from sysindexes
SELECT id
,indid
,name
,first
,CAST(SUBSTRING(first,6,1)
+ SUBSTRING(first,5,1) AS INT)
AS first_file_id
,CAST(SUBSTRING(first,4,1)
+ SUBSTRING(first,3,1)
+ SUBSTRING(first,2,1)
+ SUBSTRING(first,1,1) AS INT)
AS first_page_id
,root
,CAST(SUBSTRING(root,6,1)
+ SUBSTRING(root,5,1) AS INT)
AS root_file_id
,CAST(SUBSTRING(root,4,1)
+ SUBSTRING(root,3,1)
+ SUBSTRING(root,2,1)
+ SUBSTRING(root,1,1) AS INT)
AS root_page_id
,firstiam
,CAST(SUBSTRING(firstiam,6,1)
+ SUBSTRING(firstiam,5,1) AS INT)
AS firstiam_file_id
,CAST(SUBSTRING(firstiam,4,1)
+ SUBSTRING(firstiam,3,1)
+ SUBSTRING(firstiam,2,1)
+ SUBSTRING(firstiam,1,1) AS INT)
AS firstiam_page_id
FROM sysindexes

Wrapping Up

Hopefully, if you are digging into these, you find this useful.  As I was looking at these, there really weren’t any sources that explained how to do these – just a few forum posts here and there that discussed a little but not all of this information.  The question that I have for people reading this is – why and where would you use these values?  Is this a short cut to, or way to avoid, using DBCC IND?

If you are interested in how to map compatibility views to the their replacement objects, see my blog series Lost in Translation – Deprecated System Tables,

Related posts:

1. Discovering Binary Pages In Sys.Indexes
2. What Happened to rowmodctr from Sysindexes?
3. Lost in Translation – Deprecated System Tables – Introduction