sys-sysindexes-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
sys.sysindexes (Transact-SQL)	sys.sysindexes (Transact-SQL)	rwestMSFT	randolphwest	06/10/2016	sql	system-objects	reference	sysindexes sysindexes_TSQL sys.sysindexes sys.sysindexes_TSQL	sysindexes system table sys.sysindexes compatibility view	TSQL

sys.sysindexes (Transact-SQL)

[!INCLUDE SQL Server]

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

[!INCLUDEssnoteCompView]

Column name	Data type	Description
id	int	ID of the table to which the index belongs.
status	int	System-status information. [!INCLUDEssInternalOnly]
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. In [!INCLUDEssVersion2005] and later, rowmodctr is not fully compatible with earlier versions. For more information, see Remarks.
reserved3	int	Returns 0. [!INCLUDEssInternalOnly]
reserved4	int	Returns 0. [!INCLUDEssInternalOnly]
xmaxlen	smallint	Maximum size of a row
maxirow	smallint	Maximum size of a nonleaf index row. In [!INCLUDEssVersion2005] 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. [!INCLUDEssInternalOnly]
reserved2	int	Returns 0. [!INCLUDEssInternalOnly]
FirstIAM	binary(6)	NULL = Index is partitioned. [!INCLUDEssInternalOnly]
impid	smallint	Index implementation flag. Returns 0. [!INCLUDEssInternalOnly]
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. [!INCLUDEssInternalOnly]
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	[!INCLUDEssInternalOnly]
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.

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 SQL Server 2000 and earlier, the [!INCLUDEssDE] 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, Statistics.

• 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.

See Also

Catalog Views (Transact-SQL)
Mapping System Tables to System Views (Transact-SQL)
sys.indexes (Transact-SQL)