Chapter 1: System Tables

sysindexes

All databases

Description

sysindexes contains one row for each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each table that contains text or image columns.This table also contains one row for each function-based index or index created on a computed column.

Columns

The columns for sysindexes are:

Name	Datatype	Description
name	varchar(255) null	Index or table name.
id	int	ID of an index, or ID of table to which index belongs.
indid	smallint	0 – if a table. 1 – if a clustered index on an allpages-locked table. >1 – if a nonclustered index or a clustered index on a data-only-locked table. 255 – if text, image, text chain, or Java off-row structure (large object—or LOB—structure).
doampg	int	Obsolete
ioampg	int	Obsolete
oampgtrips	int	Number of times OAM pages cycle in the cache without being reused, before being flushed
status3	smallint	Internal system status information.
status2	smallint	Internal system status information (see Table 1-12)
ipgtrips	int	Number of times index pages cycle in the cache, without being reused, before being flushed
first	int	Obsolete
root	int	Obsolete
distribution	int	Unused. Formerly used to store the page number of the distribution page for an index.
usagecnt	smallint	Reserved
segment	smallint	Number of segment in which object resides
status	smallint	Internal system status information (see Table 1-12)
maxrowsperpage	smallint	Maximum number of rows per page
minlen	smallint	Minimum size of a row
maxlen	smallint	Maximum size of a row
maxirow	smallint	Maximum size of a non-leaf index row
keycnt	smallint	Number of keys for a clustered index on an allpages-locked table; number of keys, plus 1 for all other indexes
keys1	varbinary(255) null	Description of key columns if entry is an index
keys2	varbinary(255) null	Description of key columns if entry is an index
soid	tinyint	Sort order ID with which the index was created; 0 if there is no character data in the keys
csid	tinyint	Character set ID with which the index was created; 0 if there is no character data in the keys
base_partition	int null	Obsolete
fill_factor	smallint null	Value for the fillfactor of a table set with sp_chgattribute
res_page_gap	smallint null	Value for the reservepagegap on a table
exp_rowsize	smallint null	Expected size of data rows
keys3	varbinary(255) null	Description of key columns if entry is an index
identitygap	int null	Identity gap for a table
crdate	datetime null	Creation date
partitiontype	smallint null	1 – range 2 – hash 3 or NULL – [default] round robin 4 – list
conditionid	int null	ID of the partition condition. Null if partitiontype is round-robin or hash

Table 1-11 lists the bit representations for the status column.

**Table 1-11: Status bits in the sysindexes table status column**
Decimal	Hex	Status
1	0x1	Abort current command or trigger if attempt to insert duplicate key
2	0x2	Unique index
4	0x4	Abort current command or trigger if attempt to insert duplicate row; always 0 for data-only-locked tables
16	0x10	Clustered index
64	0x40	Index allows duplicate rows, if an allpages-locked table; always 0 for data-only-locked tables
128	0x80	Sorted object; not set for tables without clustered indexes or for text objects
512	0x200	sorted data option used in create index statement
2048	0x800	Index on primary key
32768	0x8000	Suspect index; index was created under another sort order

Table 1-12 lists the bit representations for the status2 column.

**Table 1-12: Status bits in the *sysindexes* table *status2* column**
Decimal	Hex	Status
1	0x1	Index supports foreign-key constraint
2	0x2	Index supports primary key/unique declarative constraint
4	0x4	Index includes an IDENTITY column
8	0x8	Constraint name not specified
16	0x10	Large I/Os (prefetch) not enabled for table, index, or text chain
32	0x20	Most recently used (MRU) cache strategy not enabled for table, index, or text chain
64	0x40	Ascending inserts turned on for the table
256	0x0100	Index is presorted and does not need to be copied to new extents
512	0x0200	Table is a data-only-locked table with a clustered index
8192	0x2000	Index on a data-only-locked table is suspect
32768	0x8000	The index is function-based

Indexes

Unique clustered index on id, indid

View this book as PDF