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.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.