Edit

Share via

sys.indexes (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric Preview

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 OFF.
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 OFF.
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: SQL Server 2012 (11.x) and later versions.

2 Applies to: SQL Server 2014 (12.x) and later versions.

3 Applies to: SQL Server 2017 (14.x) and later versions.

4 Applies to: SQL Server 2019 (15.x) and later versions.

5 Applies to: SQL Server 2025 (17.x) Preview and later versions.

6 Applies to: Azure SQL Database.

7 Applies to: Azure SQL Managed Instance.

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

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

Examples

The following example returns all indexes for the table Production.Product in the AdventureWorks2022 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