Find Your Keys
Now let's look again at the keys column. As I mentioned, the data looks cryptic, but the keys column value does contain meaningful information. Let's look at the keys value for the pubs database's authors table, which has a clustered index on au_id (unique) and composite nonclustered index on au_lname, au_fname:
USE pubs
SELECT keys, keycnt, datalength(keys)
FROM sysindexes
WHERE object_name(id)= 'authors'
AND dpages > 0
The length of the keys column seems to be 32 times the number of keys, so you can deduce that information for each key takes up 32 bytes. If you examine the bytes independently and see where they differ, you can gather more information about how the key columns are represented, so you could write your own code that works with the key information, for example.
The following code lists the first two groups of 32 bytes from the keys column entries for the authors table:
SELECT substring(keys, 1,32), substring(keys, 33,32)
FROM sysindexes
WHERE object_name(id)= 'authors'
AND dpages > 0
The strings for the first two columns of the nonclustered index are practically identical:
0xA703A7002800000008D00034000000000200
FFFF00000000FEFFFFFF00000000
0xA703A7001400000008D00034000000000300
FEFF00000000FDFFFEFF00000000
One difference is in the seventeenth byte, where the first index column has a 2 and the second index column has a 3. (The seventeenth byte of the second set of 32 bytes is actually byte 49.) I can extract just these seventeenth bytes by running the following query:
SELECT substring(keys, 17,1) col1, substring(keys, 49,1) col2
FROM sysindexes
WHERE object_name(id)= 'authors'
AND dpages > 0
The preceding code gives me this output:
col1 col2
---- ----
0x01 0x
0x02 0x03
Since the au_lname column has a column ID value of 2 and the au_fname column has a column ID value of 3, I can assume that the seventeenth byte of any 32-byte substring in keys indicates which column is part of the index.
Forward or Backward
There's obviously more information in the keys column, but the only other piece of information I decided to investigate was the data that shows whether the index key is stored in descending or ascending order. (Defining an index key as descending is a new feature in SQL Server 2000.) To find out where SQL Server stores the information, I created three new indexes on the authors table, using different ordering sequences for au_fname and au_lname:
CREATE INDEX desc_nameindex1 ON authors(au_lname DESC, au_fname)
CREATE INDEX desc_nameindex2 ON authors(au_lname, au_fname DESC)
CREATE INDEX desc_nameindex3 ON authors(au_lname DESC, au_fname DESC)
By using the previous query to display the 32 bytes for each key and comparing the difference in values when the key was stored ascending (the default) to when it was stored descending, I determined that a value of 4 in the 31st byte meant that the key was defined with the keyword DESC. A 0 in the 31st byte means that the key was defined as an ascending key.
I'll leave it as an exercise for you to tweak the sp_MSindexspace procedure or create a new system procedure that can extract whatever index information you're interested in. Knowing what the columns in the sysindexes table mean will help you create useful procedures for tracking and managing your own data and metadata.