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.