sys-indexes-transact-sql.md

title	sys.indexes (Transact-SQL)
description	sys.indexes contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
author	rwestMSFT
ms.author	randolphwest
ms.date	07/25/2022
ms.service	sql
ms.subservice	system-objects
ms.topic	reference
f1_keywords	sys.indexes indexes sys.indexes_TSQL indexes_TSQL
helpviewer_keywords	sys.indexes catalog view
dev_langs	TSQL
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.indexes (Transact-SQL)

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

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 rowstore (B-tree) 2 = Nonclustered rowstore (B-tree) 3 = XML 4 = Spatial 5 = Clustered columnstore index. Applies to: [!INCLUDEssSQL14] and later. 6 = Nonclustered columnstore index. Applies to: [!INCLUDEssSQL11] and later. 7 = Nonclustered hash index. Applies to: [!INCLUDEssSQL14] and later.
type_desc	nvarchar(60)	Description of index type: HEAP CLUSTERED NONCLUSTERED XML SPATIAL CLUSTERED COLUMNSTORE - Applies to: [!INCLUDEssSQL14] and later. NONCLUSTERED COLUMNSTORE - Applies to: [!INCLUDEssSQL11] and later. NONCLUSTERED HASH : NONCLUSTERED HASH indexes are supported only on memory-optimized tables. The sys.hash_indexes view shows the current hash indexes and the hash properties. For more information, see sys.hash_indexes (Transact-SQL). Applies to: [!INCLUDEssSQL14] and later.
is_unique	bit	1 = Index is unique. 0 = Index is not unique. Always 0 for clustered columnstore indexes.
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 or in-memory index.
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. Always 0 for clustered columnstore indexes.
is_unique_constraint	bit	1 = Index is part of a UNIQUE constraint. Always 0 for clustered columnstore indexes.
fill_factor	tinyint	> 0 = FILLFACTOR percentage used when the index was created or rebuilt. 0 = Default value Always 0 for clustered columnstore indexes.
is_padded	bit	1 = PADINDEX is ON. 0 = PADINDEX is OFF. Always 0 for clustered columnstore indexes.
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. Always 0 for clustered columnstore indexes.
allow_page_locks	bit	1 = Index allows page locks. 0 = Index does not allow page locks. Always 0 for clustered columnstore indexes.
has_filter	bit	1 = Index has a filter and only contains rows that satisfy the filter definition. 0 = Index does not have a filter.
filter_definition	nvarchar(max)	Expression for the subset of rows included in the filtered index. NULL for heap, non-filtered index, or insufficient permissions on the table.
compression_delay	int	> 0 = Columnstore index compression delay specified in minutes. NULL = Columnstore index rowgroup compression delay is managed automatically.
suppress_dup_key_messages	bit	1 = Index is configured to suppress duplicate key messages during an index rebuild operation. 0 = Index is not configured to suppress duplicate key messages during an index rebuild operation. Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql17-md]), [!INCLUDE ssazure-sqldb], and [!INCLUDEssazuremi]
auto_created	bit	1 = Index was created by the automatic tuning. 0 = Index was created by the user. Applies to: [!INCLUDE ssazure-sqldb]
optimize_for_sequential_key	bit	1 = Index has last-page insert optimization enabled. 0 = Default value. Index has last-page insert optimization disabled. Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql19-md]), [!INCLUDE ssazure-sqldb], and [!INCLUDEssazuremi]

Permissions

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

Examples

The following example returns all indexes for the table Production.Product in the [!INCLUDEssSampleDBnormal] database.

SELECT i.name AS index_name  
    ,i.type_desc  
    ,is_unique  
    ,ds.type_desc AS filegroup_or_partition_scheme  
    ,ds.name AS filegroup_or_partition_scheme_name  
    ,ignore_dup_key  
    ,is_primary_key  
    ,is_unique_constraint  
    ,fill_factor  
    ,is_padded  
    ,is_disabled  
    ,allow_row_locks  
    ,allow_page_locks  
FROM sys.indexes AS i  
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id  
WHERE is_hypothetical = 0 AND i.index_id <> 0   
AND i.object_id = OBJECT_ID('Production.Product');  
GO  

Next steps

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
sys.objects (Transact-SQL)
sys.key_constraints (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.partition_schemes (Transact-SQL)
Querying the SQL Server System Catalog FAQ
In-Memory OLTP (In-Memory Optimization)