Q
Problem solve Get help with specific problems with your technologies, process and projects.

Sysindexes.status vs. INDEXPROPERTY

SQL Server 2005 expert Adam Machanic explains why the INDEXPROPERTY system tool is the best way to get relevant index information.

I am trying to find out what the sysindexes.status values represent. I know that I can use sys.indexes, however I have an application that currently supports SQL 7 and 2000 using the sys.indexes table and to be consistent I would like to maintain that code.
I did some research on this topic and found a variety of conflicting information regarding what the bits in the status column mean. Given that this is an undocumented column, I can't stress enough how dangerous it is to rely on it; whether or not you get it right for one version of SQL Server, there is absolutely no guarantee that Microsoft won't change its meaning -- or drop it entirely -- in a future version!

Instead of using the status column of sysindexes, I recommend that you use the INDEXPROPERTY system function. This...

function exposes virtually all of the relevant information you might need about an index, works in all SQL Server versions from 7.0 up, and will maintain backward compatibility in future releases.

This was last published in February 2006

Dig Deeper on SQL Server Database Modeling and Design

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close