This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysindexkeys returns information on the key and included columns for indexes.  Each row represents either one of the key columns or one of the included columns that comprise the index.  The information included provides the details needed to construct the column metadata of an index.

Sysindexkeys is replaced by the catalog view sys.index_columns.  The output from the catalog view matches that of the compatibility view.  The main difference between the two is the simplicity that using the catalog view provides.  Instead of a couple extra hoops to get everything needed, which will be shown in the next section, the catalog view has everything in one place.

Query Via sysindexkeys

Like most compatibility views, the query for sysindexkeys is fairly simple.  But unlike many other compatibility views, sysindexkeys does not include a status column.  Instead, similar to sysindexes, the function INDEXKEY_PROPERTY is needed to get all of the information regarding index keys.  The function provides information on two properties – these are the column ID within the index and whether the key is sorted in a descending order.  To query the compatibility view with the function, use the query provided in Listing 1.

--Listing 1 – Query for sys.sysindexkeys
SELECT id
,indid
,colid
,keyno
,INDEXKEY_PROPERTY(id, indid, keyno, 'ColumnId') index_column_id
,INDEXKEY_PROPERTY(id, indid, keyno, 'IsDescending') is_descending_key
FROM sysindexkeys

Query via sys.index_columns

Querying the catalog view is similar to the compatibility view except that the function isn’t necessarily any longer.  Instead, the information previously available through the function is incorporated into the catalog view.  The catalog view also includes information on whether a column is an included column and where the column is within the partitioning key.  The query in Listing 2 provides a query for retrieving information from the catalog view.

--Listing 2 – Query for sys.index_columns
SELECT object_id AS id
,index_id AS indid
,column_id colid
,key_ordinal AS keyno
,index_column_id
,partition_ordinal
,is_descending_key
,is_included_column
FROM sys.index_columns

Summary

In this post, the use of sys.index_columns was contrasted against sysindexkeys.  The post demonstrated how using the catalog view can provide the same, yet in a more simple format, information that is available in the compatibility view.  After reading all of this, do you see any reason to continue using sysindexkeys?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysforeignkeys
2. Lost in Translation – Deprecated System Tables – sysfulltextcatalogs
3. Lost in Translation – Deprecated System Tables – sysindexes