sys-indexes-transact-sql.md

title	sys.indexes (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	04/18/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	sys.indexes indexes sys.indexes_TSQL indexes_TSQL
dev_langs	TSQL
helpviewer_keywords	sys.indexes catalog view
ms.assetid	066bd9ac-6554-4297-88fe-d740de1f94a8
author	stevestein
ms.author	sstein
manager	craigg
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.indexes (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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 4 = Spatial 5 = Clustered columnstore index. Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 6 = Nonclustered columnstore index. Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. 7 = Nonclustered hash index. Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent].
type_desc	nvarchar(60)	Description of index type: HEAP CLUSTERED NONCLUSTERED XML SPATIAL CLUSTERED COLUMNSTORE - Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. NONCLUSTERED COLUMNSTORE - Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. 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] through [!INCLUDEssCurrent].
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 or non-filtered index.
auto_created	bit	1 = Index was created by the automatic tuning. 0 = Index was created by the user.

Permissions

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

Examples

The following example returns all iindexes 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  
  

See Also

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)