sys.index_columns (Transact-SQL)

Contains one row per column that is part of a sys.indexes index or unordered table (heap).

Column name

Data type

Description

object_id

int

ID of the object the index is defined on.

index_id

int

ID of the index in which the column is defined.

index_column_id

int

ID of the index column. index_column_id is unique only within index_id.

column_id

int

ID of the column in object_id.

0 = Row Identifier (RID) in a nonclustered index.

column_id is unique only within object_id.

key_ordinal

tinyint

Ordinal (1-based) within set of key-columns.

0 = Not a key column, or is an XML index, xVelocity memory optimized columnstore, or spatial index.

Note

An XML or spatial index cannot be a key because the underlying columns are not comparable, meaning that their values cannot be ordered.

partition_ordinal

tinyint

Ordinal (1-based) within set of partitioning columns.

0 = Not a partitioning column.

distribution_ordinal

bit

Reserved for future use.

is_descending_key

bit

1 = Index key column has a descending sort direction.

0 = Index key column has an ascending sort direction.

Does not apply to columnstore indexes which return 0.

is_included_column

bit

1 = Column is a nonkey column added to the index by using the CREATE INDEX INCLUDE clause, or is a column that is part of a columnstore index.

0 = Column is not an included column.

Columns implicitly added because they are part of the clustering key are not listed in sys.index_columns.

Columns implicitly added because they are a partitioning column are returned as 0.

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

See Also

Reference

Object Catalog Views (Transact-SQL)

Catalog Views (Transact-SQL)

sys.indexes (Transact-SQL)

sys.objects (Transact-SQL)

CREATE INDEX (Transact-SQL)

sys.columns (Transact-SQL)

Concepts

Querying the SQL Server System Catalog FAQ