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 sysindexes returns one row for every index in a database.  The information in the row provides the details for understanding how the index is structured, and knowing some base statistics on the index.

Sysindexes is primarily replaced by the catalog view sys .indexes.  This catalog view also returns one row per index in the database, with most of the details being related to the metadata about the index.  Not all of the information from sysindexes is included in sys.indexes.  Some other catalog views and dynamic management objects that provide information that is in sysindexes includes sys.index_columns, sys.stats, sys.dm_db_partition_stats, and sys.dm_db_index_operational_stats

Obsolete Columns

The first thing to note with sysindexes is that there are a number of columns that are included but don’t return any meaningful data.  These columns always either return NULL values or 0.  Since they are just there for the sake of existing, there won’t be any mapping of the columns in sys.indexes.  The columns that are obsolete are the following:

• reserved2
• reserved3
• reserved4
• StatVersion
• impid
• pgmodctr
• keys
• statblob

If an application is still using these columns with sysindexes, it should definitely be reviewed.  Anything relying on these columns will not be executing business rules and processes as expected.  Unless, of course, the non-values that these columns provide are what is needed.  But, that still begs the question of why they are being used.

Status Column

The next item to review is the status column.  Like many other compatibility views, sysindexes contains a column named status that contains a number of properties about the index.  While the values stored in the column are not detailed in Books Online, they can be derived by querying spt_values in the master database.

The values contained in the status column are:

• 0×1: Ignore duplicate keys in index
• 0×2: Is unique
• 0×4: Ignore duplicate rows in the index
• 0×10: Is clustered
• 0×20: Is hypothetical
• 0×40: Is only statistics
• 0×800: Is primary key
• 0×1000: Is unique key
• 0×800000: Index was auto created
• 0×1000000: Statistics no recompute

INDEXPROPERTY

Some index properties aren’t directly available through sysindexes, but they are available in sys.indexes.  To access these values for sysindexes, the INDEXPROPERTY() function is necessary.  This function uses the object id, index name, and property name to retrieve the properties for the index.

Complex Columns

The last thing to note before reviewing a query against sysindexes is a few of the complex columns that are returned from sysindexes.  These columns are considered complex because they don’t return general metadata about the index, instead, they provide low-level information that is more often defined as statistics and internals information. The columns that fall into this classification are:

• First
• Root
• FirstIAM
• Rowmodctr

Rather than include the information about mapping and translating these columns in this post, they are discussed in three other posts that were previously published.  The posts are:

• What Happened to rowmodctr from Sysindexes?
• Decoding first, root, and FirstIAM in Sysindexes
• Discovering Binary Pages In Sys.Indexes

Query Via sysindexes

As the lead up to the section has undoubtedly shown, querying sysindexes is a bit more complicated with that querying other compatibility views.  Some of the information returned is stored in either a status column or the INDEXPROPERTY column, and sometimes both.  There are also a few columns that are obsolete or more complex than one would look at in this post.  In the end, these factors result in a query, provided in Listing 1, that is missing a few of the columns and has a couple of the data elements from multiple sources.  Overall, though, the query should look similar or contain all of the parts that queries using sysindexes are currently using.

--Listing 1 – Query for sys.sysindexes
SELECT groupid
,id
,name
,indid
--,INDEXPROPERTY (i.id,i.name,'IndexID') AS indid
,CONVERT(INT,i.status & 0x10) / 16 AS is_clustered
--,INDEXPROPERTY (i.id,i.name,'IsClustered') AS is_clustered
,CONVERT(INT,i.status & 0x2) / 2 AS is_unique
--,INDEXPROPERTY (i.id,i.name,'IsUnique') AS is_unique
,CONVERT(INT,i.status & 0x1) / 1 AS IGNORE_DUP_KEY
,CONVERT(INT,i.status & 0x4) / 4 AS ignore_duplicate_rows
,CONVERT(INT,i.status & 0x800) / 2048 AS is_primary_key
,CONVERT(INT,i.status & 0x20) / 32 AS is_hypothetical
--,INDEXPROPERTY (i.id,i.name,'IsHypothetical') AS is_hypothetical
,CONVERT(INT,i.status & 0x1000) / 4096 AS is_unique_constraint
,INDEXPROPERTY (i.id,i.name,'IsPadIndex') AS is_padded
,INDEXPROPERTY (i.id,i.name,'IsDisabled') AS is_disabled
,INDEXPROPERTY (i.id,i.name,'IndexFillFactor') AS IndexFillFactor
,OrigFillFactor
,CONVERT(INT, i.status & 0x1000000) / 16777216 AS stats_no_recompute
,CONVERT(INT,i.status & 0x800000) / 8388608 AS auto_create
,CONVERT(INT, i.status & 0x1000000) / 16777216 AS stats_no_recompute
,INDEXPROPERTY (i.id,i.name,'IsAutoStatistics') AS IsAutoStatistics
,INDEXPROPERTY (i.id,i.name,'IsStatistics') AS IsStatistics
,status
,dpages
,reserved
,used
,rowcnt
,rows
,minlen
,maxlen
,xmaxlen
,keycnt
,maxirow
,INDEXPROPERTY (i.id,i.name,'IsRowLockDisallowed') AS IsRowLockDisallowed
,INDEXPROPERTY (i.id,i.name,'IsPageLockDisallowed') AS IsPageLockDisallowed
,lockflags
,rowmodctr
FROM sysindexes i

Query via sys.indexes

There is a bit more effort to returning the information in sysindexes through sys.indexes.  In some ways, querying with sys.indexes is easier since the status column doesn’t exist in sys.indexes and most of the function values are available in the catalog view.  But there are a few other columns where the information is not stored in sys.indexes and other compatibility views and dynamic management objects are required.  And lastly, there are a couple columns where the information available in the compatibility view is just not available anywhere any longer.

Starting with last things first, the column OrigFillFactor from sysindexes is no longer stored in SQL Server’s metadata.  This column would return the original fill factor used by an index.  The only values currently available are the current fill factor.  In general this doesn’t seem to be a big loss, since the use of original fill factor would be extremely limited in maintaining an index; If there was any use at all.

The next bit of complexity exists with the minlen, maxlen, and xmaxlen columns.  To start simply, the column maxlen always returns the value 8,000.  The values minlen and xmaxlen return the minimum and maximum lenths for the keys in an index.  The easiest way to get these is by using undocumented properties that are part of INDEXPROPERTY.

When it comes to the columns dpages, reserved, and used_page_count, the best way to retireve this information is from the dynamic management view sys.dm_db_partition_stats.  This view returns information about how an index is using pages in the database.  Of course, for sysindexes, the results do vary based on the type of the index; which requires using a switch between heaps, clustered indexes, and other index types.

The column lockflags from sysindexes also includes a bit of calculation.  This flag is based on the settings for whether row or page locks are disallowed.  Depending on those settings, the value can be 0 to 3.  If row locks are disallowed, the value 1 is assigned.  Then if page locks are disallowed, the value 2 is added to the previous value.

The last piece from sysindexes that needs to be calculated for sys.indexes, is the number of columns in the keys for each index, (which is stored in the keys column).  This column requires querying sys.index_columns for each index.  The one catch with the count, is that for non-clustered indexes, the count of the key columns from the clustered index are included in the column count.

With the logic from this section, the query against sys.indexes to return the same information as sysindexes can be created.  Generally, the replacement query will be similar to the one included in Listing 2.

--Listing 2 – Query for sys.indexes
SELECT i.data_space_id AS groupid
,i.object_id AS id
,i.name
,i.index_id AS indid
,i.type
,i.type_desc
,i.is_unique
,i.ignore_dup_key
,i.is_primary_key
,i.is_hypothetical
,i.is_unique_constraint
,i.is_padded
,i.is_disabled
,INDEXPROPERTY (i.object_id,i.name,'IndexFillFactor') AS IndexFillFactor
,i.fill_factor
,S.auto_created
,s.no_recompute
,s.auto_created
,x.in_row_data_page_count AS dpages
,CASE WHEN i.index_id IN (1,0) THEN y.reserved_page_count ELSE x.reserved_page_count END AS reserved
,CASE WHEN i.index_id IN (1,0) THEN y.used_page_count ELSE x.used_page_count END AS used_page_count
,y.row_count AS rowcnt
,x.row_count AS rows
,INDEXPROPERTY(i.object_id,i.name,'minlen') AS minlen
,8000 AS maxlen
,INDEXPROPERTY(i.object_id,i.name,'maxlen') AS xmaxlen
,((SELECT COUNT(*)
FROM sys.index_columns ic
WHERE is_included_column = 0
AND i.object_id = ic.object_id
AND ic.index_id IN (0,1))
+ (SELECT COUNT(*)
FROM sys.index_columns ic
WHERE is_included_column = 0
AND i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND i.index_id NOT IN (0,1))) AS keycnt
,NULL AS maxirow
,i.allow_row_locks
,i.allow_page_locks
,CASE WHEN i.allow_row_locks=0 THEN 1 ELSE 0 END+ CASE WHEN i.allow_page_locks=0 THEN 2 ELSE 0 END AS lockflags
,z.rowmodctr
,i.has_filter
,i.filter_definition
FROM sys.indexes i
LEFT OUTER JOIN sys.stats s ON i.object_id = s.object_id AND i.name = s.name
OUTER APPLY (SELECT SUM(in_row_data_page_count) AS in_row_data_page_count
,SUM(used_page_count) AS used_page_count
,SUM(reserved_page_count) AS reserved_page_count
,SUM(row_count) AS row_count
FROM sys.dm_db_partition_stats ps
WHERE i.object_id = ps.object_id
AND i.index_id = ps.index_id) x
OUTER APPLY (SELECT SUM(used_page_count) AS used_page_count
,SUM(reserved_page_count) AS reserved_page_count
,SUM(CASE WHEN index_id IN (1,0) THEN row_count ELSE 0 END) AS row_count
FROM sys.dm_db_partition_stats ps
WHERE i.object_id = ps.object_id) y
LEFT OUTER JOIN (SELECT object_id
,index_id
,leaf_insert_count
+ leaf_delete_count
+ leaf_update_count
+ leaf_ghost_count AS rowmodctr
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL)) z ON i.object_id = z.object_id AND i.index_id = z.index_id
WHERE i.object_id IN (SELECT OBJECT_ID FROM sys.tables)

Summary

This post looked at the differences between sysindexes and sys.indexes, and provided information about how to map one to the other.  Unlike other compatibility views, the change between these two objects is a bit more involved.  But that change is necessary, based on deprecation, and doing so will help produce information that are more clear and concise, though the query to do so may not be so concise.  On other the big benefits to changing from sysindexes to sys,indexes, is the transition to using index data from the right place.  Meaning that the summary information that was stored only in sysindexes before is now available from more detailed sources and better tools can be written using this information.

After reading all of this, do you see any reason to continue using sysindexes?  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 – sysindexkeys
2. Lost in Translation – Deprecated System Tables – sysconfigures
3. Lost in Translation – Deprecated System Tables – syscurconfigs