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

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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 描述Description
object_idobject_id intint 這個索引所屬物件的識別碼。ID of the object to which this index belongs.
namename sysnamesysname 索引的名稱。Name of the index. name只有在物件內才是唯一的。name is unique only within the object.

NULL = 堆積NULL = Heap
index_idindex_id intint 索引的識別碼。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
型別type 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) 及更新版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

6 = 非叢集資料行存放區索引。6 = Nonclustered columnstore index. 適用對象SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

7 = 非叢集雜湊索引。7 = Nonclustered hash index. 適用對象SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.
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) 和更新版本。CLUSTERED COLUMNSTORE - Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

非叢集資料行存放區-適用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和更新版本。NONCLUSTERED COLUMNSTORE - Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

非叢集雜湊:只有記憶體優化資料表支援非叢集雜湊索引。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.databases (。 hash_indexes transact-sql)For more information, see sys.hash_indexes (Transact-SQL). 適用對象SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.
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 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.

PermissionsPermissions

目錄檢視內中繼資料的可見性會限制在使用者所擁有的安全性實體,或已授與使用者某些權限的安全性實體。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

下列範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫中 Production.Product 資料表的所有索引。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)
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)