TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.indexes (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

Column nameData typeDescription
object_idintID of the object to which this index belongs.
namesysnameName of the index. name is unique only within the object.

NULL = Heap
index_idintID of the index. index_id is unique only within the object.

0 = Heap

1 = Clustered index

> 1 = Nonclustered index
typetinyintType of index:

0 = Heap

1 = Clustered

2 = Nonclustered

3 = XML

4 = Spatial

5 = Clustered columnstore index. Applies to: SQL Server 2014 through SQL Server 2016.

6 = Nonclustered columnstore index. Applies to: SQL Server 2012 through SQL Server 2016.

7 = Nonclustered hash index. Applies to: SQL Server 2014 through SQL Server 2016.
type_descnvarchar(60)Description of index type:

HEAP

CLUSTERED

XML

SPATIAL

CLUSTERED COLUMNSTORE - Applies to: SQL Server 2014 through SQL Server 2016.

NONCLUSTERED COLUMNSTORE - Applies to: SQL Server 2012 through SQL Server 2016.

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: SQL Server 2014 through SQL Server 2016.
is_uniquebit1 = Index is unique.

0 = Index is not unique.

Always 0 for clustered columnstore indexes.
data_space_idintID 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_keybit1 = IGNORE_DUP_KEY is ON.

0 = IGNORE_DUP_KEY is OFF.
is_primary_keybit1 = Index is part of a PRIMARY KEY constraint.

Always 0 for clustered columnstore indexes.
is_unique_constraintbit1 = Index is part of a UNIQUE constraint.

Always 0 for clustered columnstore indexes.
fill_factortinyint> 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value

Always 0 for clustered columnstore indexes.
is_paddedbit1 = PADINDEX is ON.

0 = PADINDEX is OFF.

Always 0 for clustered columnstore indexes.
is_disabledbit1 = Index is disabled.

0 = Index is not disabled.
is_hypotheticalbit1 = 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_locksbit1 = Index allows row locks.

0 = Index does not allow row locks.

Always 0 for clustered columnstore indexes.
allow_page_locksbit1 = Index allows page locks.

0 = Index does not allow page locks.

Always 0 for clustered columnstore indexes.
has_filterbit1 = Index has a filter and only contains rows that satisfy the filter definition.

0 = Index does not have a filter.
filter_definitionnvarchar(max)Expression for the subset of rows included in the filtered index.

NULL for heap or non-filtered index.

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

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

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)

Community Additions

ADD
Show:
© 2016 Microsoft