Skip to content

Latest commit

 

History

History
83 lines (70 loc) · 5.29 KB

sys-index-columns-transact-sql.md

File metadata and controls

83 lines (70 loc) · 5.29 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.index_columns (Transact-SQL)
sys.index_columns contains one row per column that is part of an index or unordered table (heap).
rwestMSFT
randolphwest
10/14/2022
sql
system-objects
reference
sys.index_columns
sys.index_columns_TSQL
index_columns
index_columns_TSQL
sys.index_columns catalog view
TSQL
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.index_columns (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Contains one row per column that is part of an 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, a columnstore index, or a 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. A clustered columnstore index can have at most one partitioning column.

0 = Not a partitioning column.
is_descending_key bit 1 = Index key column has a descending sort direction.

0 = Index key column has an ascending sort direction, or the column is part of a columnstore or hash index.
is_included_column bit 1 = Column is a non-key column added to the index by using the CREATE INDEX INCLUDE clause, or the column is part of a columnstore index.

0 = Column is not an included column.

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

Columns implicitly added because they're a partitioning column are returned as 0.
column_store_order_ordinal tinyint Applies to: [!INCLUDEssazuresynapse-md] and [!INCLUDEsssql22-md]
Ordinal (1-based) within set of order columns in an ordered clustered columnstore index. For more on ordered clustered columnstore indexes, see Columnstore index design guidance.

Permissions

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

Examples

The following example returns all indexes and index columns for the table Production.BillOfMaterials.

USE AdventureWorks2022;
GO
SELECT i.name AS index_name
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name
    ,ic.index_column_id
    ,ic.key_ordinal
,ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('Production.BillOfMaterials');

[!INCLUDEssResult]

index_name                                                 column_name        index_column_id key_ordinal is_included_column
---------------------------------------------------------- -----------------  --------------- ----------- -------------
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate ProductAssemblyID  1               1           0
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate ComponentID        2               2           0
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate StartDate          3               3           0
PK_BillOfMaterials_BillOfMaterialsID                       BillOfMaterialsID  1               1           0
IX_BillOfMaterials_UnitMeasureCode                         UnitMeasureCode    1               1           0
  
(5 row(s) affected)

Next steps