sp_helpindex: Remember what you’re missing

Back in December I blogged about the usefulness of the DISABLE INDEX option which was added in SQL Server 2005. In that post I mentioned using sp_helpindex to verify which indexes existed for a table and what columns were in those indexes. If I had to rebuild an index in SQL Server 2000, this option allowed me to verify the columns which comprised the index. Andrew Kelly( blog | @gunneyk ) reminded me that the sp_helpindex output does not show included columns or make note if the index is filtered, which is important when using SQL 2005 and higher.

I actually wondered how I had forgotten that. I’ve read Kimberly Tripp’s post detailing the sp_helpindex8 that she wrote, which you can find here. My lame excuse is that in the application I support, we do not utilize included columns in our indexes, or filtered indexes, by default. Therefore, I am not usually on the lookout for them. But that’s not a good excuse.

*As an aside, something I have learned is that once you forget something you will always remember it. That may sound backwards, but if you forget that you should be regularly restoring your backups to verify them, and they fail on you, you will always remember to verify your backups after that. As such, I will forever remember that sp_helpindex doesn’t have EVERYTHING I might need, and I really should be using Kimberly’s sp_helpindex8 every time.

If you’ve started to wonder why the SQL Server team hasn’t changed sp_helpindex to show additional index features, then I encourage you to check out the following three Connect articles and vote if you are so inclined:

sp_helpindex results should include the Filter expression of Filter Indices
sp_helpindex to show included columms *what I find interesting about this one is that the workaround references Kimberly’s sp_helpindex (version 2)
SQL Server 2005: sp_helpindex

Just for fun, I thought I would include some screen shots to show what you’re missing if you are relying on sp_helpindex to tell you everything. All statements were executed against the AdventureWorks database in SQL Server 2008 R2.

Here are the default indexes for the SalesOrderDetail table, as shown with sp_helpindex:

no_index

I created two indexes, one with included columns, the other with a filter:

{code}

— filtered index

create nonclustered index FI_SalesOrderDetail_OrderQty_LineTotal

on Sales.SalesOrderDetail (OrderQty, LineTotal)

where OrderQty > 9;

— index with included columns

create nonclustered index INC_SalesOrderDetail_OrderQty

on Sales.SalesOrderDetail (OrderQty)

include (LineTotal);

{/code}

Here is the output from sp_helpindex:

with index_default

And here is the much more useful output Kimberly’s sp_helpindex8:

with index_helpindex8

Note that I didn’t even discuss two additional features of sp_helpindex8 output that are not shown in the above image: columns in tree and columns in leaf. These are detailed more in the original post.

Cleanup code:

{code}

drop index Sales.SalesOrderDetail.FI_SalesOrderDetail_OrderQty_LineTotal;

drop index Sales.SalesOrderDetail.INC_SalesOrderDetail_OrderQty;

{/code}