sys.indexes (Transact-SQL)sys.indexes (Transact-SQL)

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure 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 name 数据类型Data type DescriptionDescription
object_idobject_id intint 该索引所属对象的 ID。ID of the object to which this index belongs.
名称name sysnamesysname 索引的名称。Name of the index. 名称是只在对象中唯一的。name is unique only within the object.

NULL = 堆NULL = Heap
index_idindex_id intint 索引的 ID。ID of the index. index_id是只在对象中唯一的。index_id is unique only within the object.

0 = 堆0 = Heap

1 = 聚集索引1 = Clustered index

> 1 = 非聚集索引> 1 = Nonclustered index
typetype tinyinttinyint 索引的类型:Type of index:

0 = 堆0 = Heap

1 = 聚集1 = Clustered

2 = 非聚集2 = Nonclustered

3 = XML3 = XML

4 = 空间4 = Spatial

5 = 聚集列存储索引。5 = Clustered columnstore index. 适用范围SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

6 = 非聚集列存储索引。6 = Nonclustered columnstore index. 适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

7 = 非聚集哈希索引。7 = Nonclustered hash index. 适用范围SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.
type_desctype_desc nvarchar(60)nvarchar(60) 索引类型的说明:Description of index type:

HEAPHEAP

CLUSTEREDCLUSTERED

NONCLUSTEREDNONCLUSTERED

XMLXML

SPATIALSPATIAL

聚集列存储-适用范围:SQL Server 2014 (12.x)SQL Server 2014 (12.x)通过SQL Server 2017SQL Server 2017CLUSTERED COLUMNSTORE - Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

非聚集列存储-适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)通过SQL Server 2017SQL Server 2017NONCLUSTERED COLUMNSTORE - Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

非聚集哈希:仅在内存优化表支持非聚集哈希索引。NONCLUSTERED HASH : NONCLUSTERED HASH indexes are supported only on memory-optimized tables. sys.hash_indexes 视图显示当前哈希索引和哈希属性。The sys.hash_indexes view shows the current hash indexes and the hash properties. 有关详细信息,请参阅sys.hash_indexes (TRANSACT-SQL)For more information, see sys.hash_indexes (Transact-SQL). 适用范围SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.
is_uniqueis_unique bitbit 1 = 索引是唯一的。1 = Index is unique.

0 = 索引不是唯一的。0 = Index is not unique.

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
data_space_iddata_space_id intint 此索引的数据空间 ID。ID of the data space for this index. 数据空间是文件组或分区方案。Data space is either a filegroup or partition scheme.

0 = object_id是表值函数或内存中索引。0 = object_id is a table-valued function or in-memory index.
ignore_dup_keyignore_dup_key bitbit 1 = IGNORE_DUP_KEY 是 ON。1 = IGNORE_DUP_KEY is ON.

0 = IGNORE_DUP_KEY 是 OFF。0 = IGNORE_DUP_KEY is OFF.
is_primary_keyis_primary_key bitbit 1 = 索引是 PRIMARY KEY 约束的一部分。1 = Index is part of a PRIMARY KEY constraint.

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
is_unique_constraintis_unique_constraint bitbit 1 = 索引是 UNIQUE 约束的一部分。1 = Index is part of a UNIQUE constraint.

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
fill_factorfill_factor tinyinttinyint > 0 = 使用已创建或重新生成索引的 FILLFACTOR 百分比。> 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = 默认值0 = Default value

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
is_paddedis_padded bitbit 1 = PADINDEX 是 ON。1 = PADINDEX is ON.

0 = PADINDEX 是 OFF。0 = PADINDEX is OFF.

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
is_disabledis_disabled bitbit 1 = 禁用索引。1 = Index is disabled.

0 = 不禁用索引。0 = Index is not disabled.
is_hypotheticalis_hypothetical bitbit 1 = 索引是假设的,不能直接用作数据访问路径。1 = Index is hypothetical and cannot be used directly as a data access path. 假设的索引包含列级统计信息。Hypothetical indexes hold column-level statistics.

0 = 索引不是假设的。0 = Index is not hypothetical.
allow_row_locksallow_row_locks bitbit 1 = 索引允许行锁。1 = Index allows row locks.

0 = 索引不允许行锁。0 = Index does not allow row locks.

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
allow_page_locksallow_page_locks bitbit 1 = 索引允许页锁。1 = Index allows page locks.

0 = 索引不允许页锁。0 = Index does not allow page locks.

对于聚集列存储索引始终为 0。Always 0 for clustered columnstore indexes.
has_filterhas_filter bitbit 1 = 索引具有一个筛选器,且仅包含符合筛选器定义的行。1 = Index has a filter and only contains rows that satisfy the filter definition.

0 = 索引不具有筛选器。0 = Index does not have a filter.
filter_definitionfilter_definition nvarchar(max)nvarchar(max) 包含在筛选索引中的行子集的表达式。Expression for the subset of rows included in the filtered index.

对于堆或非筛选索引,其值为 NULL。NULL for heap or non-filtered index.
auto_createdauto_created bitbit 1 = 索引已通过自动优化。1 = Index was created by the automatic tuning.

0 = 索引由用户创建。0 = Index was created by the user.
optimize_for_sequential_keyoptimize_for_sequential_key bitbit 1 = 索引的最后一页插入优化已启用。1 = Index has last-page insert optimization enabled.

0 = 默认值。0 = Default value. 索引已禁用的最后一页插入优化。Index has last-page insert optimization disabled.

权限Permissions

目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。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. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

示例Examples

下面的示例返回所有索引的表Production.ProductAdventureWorks2012AdventureWorks2012数据库。The following example returns all indexes for the table Production.Product in the AdventureWorks2012AdventureWorks2012 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

对象目录视图 (Transact-SQL) Object Catalog Views (Transact-SQL)
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
sys.index_columns (Transact-SQL) sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL) sys.xml_indexes (Transact-SQL)
sys.objects (Transact-SQL) sys.objects (Transact-SQL)
sys.key_constraints (Transact-SQL) sys.key_constraints (Transact-SQL)
sys.filegroups (Transact-SQL) sys.filegroups (Transact-SQL)
sys.partition_schemes (Transact-SQL) sys.partition_schemes (Transact-SQL)
查询 SQL Server 系统目录常见问题 Querying the SQL Server System Catalog FAQ
内存中 OLTP(内存中优化)In-Memory OLTP (In-Memory Optimization)