sys.sysindexes (Transact-SQL)

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead.

Important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name

Data type

Description

id

int

ID of the table to which the index belongs.

status

int

System-status information.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

first

binary(6)

Pointer to the first or root page.

Unused when indid = 0.

NULL = Index is partitioned when indid > 1.

NULL = Table is partitioned when indid is 0 or 1.

indid

smallint

ID of the index:

0 = Heap

1 = Clustered index

>1 = Nonclustered index

root

binary(6)

For indid >= 1, root is the pointer to the root page.

Unused when indid = 0.

NULL = Index is partitioned when indid > 1.

NULL = Table is partitioned when indid is 0 or 1.

minlen

smallint

Minimum size of a row.

keycnt

smallint

Number of keys.

groupid

smallint

Filegroup ID on which the object was created.

NULL = Index is partitioned when indid > 1.

NULL = Table is partitioned when indid is 0 or 1.

dpages

int

For indid = 0 or indid = 1, dpages is the count of data pages used.

For indid > 1, dpages is the count of index pages used.

0 = Index is partitioned when indid > 1.

0 = Table is partitioned when indid is 0 or 1.

Does not yield accurate results if row-overflow occurs.

reserved

int

For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data.

For indid > 1, reserved is the count of pages allocated for the index.

0 = Index is partitioned when indid > 1.

0 = Table is partitioned when indid is 0 or 1.

Does not yield accurate results if row-overflow occurs.

used

int

For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data.

For indid > 1, used is the count of pages used for the index.

0 = Index is partitioned when indid > 1.

0 = Table is partitioned when indid is 0 or 1.

Does not yield accurate results if row-overflow occurs.

rowcnt

bigint

Data-level row count based on indid = 0 and indid = 1.

0 = Index is partitioned when indid > 1.

0 = Table is partitioned when indid is 0 or 1.

rowmodctr

int

Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.

0 = Index is partitioned when indid > 1.

0 = Table is partitioned when indid is 0 or 1.

For a heap (a table without a clustered index, indid 0), the rowmodctr will always increase. It is not the number of rows modified since the last statistics update.

For a clustered index or non-clustered index, this counter will only track changes made to the leading column since the last index update.

In SQL Server 2005 and later, rowmodctr is not fully compatible with earlier versions. For more information, see Remarks.

xmaxlen

smallint

Maximum size of a row

maxirow

smallint

Maximum size of a nonleaf index row.

In SQL Server 2005 and later, maxirow is not fully compatible with earlier versions.

OrigFillFactor

tinyint

Original fill factor value used when the index was created. This value is not maintained; however, it can be helpful if you have to re-create an index and do not remember the fill factor value that was used.

StatVersion

tinyint

Returns 0.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

reserved2

int

Returns 0.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

FirstIAM

binary(6)

NULL = Index is partitioned.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

impid

smallint

Index implementation flag.

Returns 0.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

lockflags

smallint

Used to constrain the considered lock granularities for an index. For example, to minimize locking cost, a lookup table that is essentially read-only could be set up to do only table-level locking.

pgmodctr

int

Returns 0.

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

keys

varbinary(816)

List of the column IDs of the columns that make up the index key.

Returns NULL.

To display the index key columns, use sys.sysindexkeys.

name

sysname

Name of the index or statistic. Returns NULL when indid = 0. Modify your application to look for a NULL heap name.

statblob

image

Statistics binary large object (BLOB).

Returns NULL.

maxlen

int

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

rows

int

Data-level row count based on indid = 0 and indid = 1, and the value is repeated for indid >1.

Remarks

Columns defined as reserved should not be used.

In earlier versions of SQL Server, each table that had at least one text, ntext, or image column also had a row in sysindexes with indid = 255. This index id no longer exists. When a table or index has one or more large object (LOB) data types, one LOB_DATA allocation unit per partition is allocated to manage the storage of that data. For more information, see Table and Index Organization.

The columns dpages, reserved, and used will not return accurate results if the table or index contains data in the ROW_OVERFLOW allocation unit. In addition, the page counts for each index are tracked separately and are not aggregated for the base table. To view page counts, use the sys.allocation_units or sys.partitions catalog views, or the sys.dm_db_partition_stats dynamic management view.

In earlier versions of SQL Server, the Database Engine maintained row-level modification counters. Such counters are now maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.

If you use the value in rowmodctr to determine when to update statistics, consider the following solutions:

  • Do nothing. The new rowmodctr value will frequently help you determine when to update statistics because the behavior is reasonably close to the results of earlier versions.

  • Use AUTO_UPDATE_STATISTICS. For more information see, Using Statistics to Improve Query Performance.

  • Use a time limit to determine when to update statistics. For example, every hour, every day, or every week.

  • Use application-level information to determine when to update statistics. For example, every time the maximum value of an identity column changes by more than 10,000, or every time a bulk insert operation is performed.