Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

sys.indexes (Transact-SQL)

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

Column name Data type Description

object_id

int

ID of the object to which this index belongs.

name

sysname

Name of the index. name is unique only within the object.

NULL = Heap

index_id

int

ID of the index. index_id is unique only within the object.

0 = Heap

1 = Clustered index

> 1 = Nonclustered index

type

tinyint

Type of index:

0 = Heap

1 = Clustered

2 = Nonclustered

3 = XML

type_desc

nvarchar(60)

Description of index type:

HEAP

CLUSTERED

NONCLUSTERED

XML

is_unique

bit

1 = Index is unique.

0 = Index is not unique.

data_space_id

int

ID of the data space for this index. Data space is either a filegroup or partition scheme.

0 = object_id is a table-valued function.

ignore_dup_key

bit

1 = IGNORE_DUP_KEY is ON.

0 = IGNORE_DUP_KEY is OFF.

is_primary_key

bit

1 = Index is part of a PRIMARY KEY constraint.

is_unique_constraint

bit

1 = Index is part of a UNIQUE constraint.

fill_factor

tinyint

> 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value

is_padded

bit

1 = PADINDEX is ON.

0 = PADINDEX is OFF.

is_disabled

bit

1 = Index is disabled.

0 = Index is not disabled.

is_hypothetical

bit

1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.

0 = Index is not hypothetical.

allow_row_locks

bit

1 = Index allows row locks.

0 = Index does not allow row locks.

allow_page_locks

bit

1 = Index allows page locks.

0 = Index does not allow page locks.

Community Additions

ADD
Show:
© 2015 Microsoft