Skip to content

sys-indexes-transact-sql.md

Latest commit

 

History

History
112 lines (94 loc) · 7.21 KB

sys-indexes-transact-sql.md

File metadata and controls

112 lines (94 loc) · 7.21 KB
title sys.indexes (Transact-SQL)
description The sys.indexes catalog view 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 11/24/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
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 || =fabric-sqldb

sys.indexes (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]

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 2
6 = Nonclustered columnstore index 1
7 = Nonclustered hash index 2
9 = JSON 5
type_desc nvarchar(60) Description of index type:

- HEAP
- CLUSTERED
- NONCLUSTERED
- XML
- SPATIAL
- CLUSTERED COLUMNSTORE 2
- NONCLUSTERED COLUMNSTORE 1
- NONCLUSTERED HASH 2, 8
- JSON 5
is_unique bit 1 = Index is unique.
0 = Index isn't 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 isn't disabled.
is_hypothetical bit 1 = Index is hypothetical and can't be used directly as a data access path. Hypothetical indexes hold column-level statistics.

0 = Index isn't hypothetical.
allow_row_locks bit 1 = Index allows row locks.
0 = Index doesn't allow row locks.

Always 0 for clustered columnstore indexes.
allow_page_locks bit 1 = Index allows page locks.
0 = Index doesn't 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 doesn't have a filter.
filter_definition nvarchar(max) Expression for the subset of rows included in the filtered index.

NULL for heap, nonfiltered 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 3, 6, 7 bit 1 = Index is configured to suppress duplicate key messages during an index rebuild operation.

0 = Index isn't configured to suppress duplicate key messages during an index rebuild operation.
auto_created 6 bit 1 = Index was created by the automatic tuning.
0 = Index was created by the user.
optimize_for_sequential_key 4, 6, 7 bit 1 = Index has last-page insert optimization enabled.
0 = Default value. Index has last-page insert optimization disabled.

1 Applies to: [!INCLUDE sssql11-md] and later versions.

2 Applies to: [!INCLUDE sssql14-md] and later versions.

3 Applies to: [!INCLUDE sssql17-md] and later versions.

4 Applies to: [!INCLUDE sssql19-md] and later versions.

5 Applies to: [!INCLUDE sssql25-md] and later versions.

6 Applies to: [!INCLUDE ssazure-sqldb].

7 Applies to: [!INCLUDE ssazuremi].

8 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.

Permissions

[!INCLUDE ssCatViewPerm] For more information, see Metadata visibility configuration.

Examples

The following example returns all indexes for the table Production.Product in the [!INCLUDE ssSampleDBnormal] 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

Related content