索引Indexes

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

可用的索引類型Available index types

下表列出 SQL ServerSQL Server 中可用的索引類型,並提供其他資訊的連結。The following table lists the types of indexes available in SQL ServerSQL Server and provides links to additional information.

索引類型Index type DescriptionDescription 其他資訊Additional information
雜湊Hash 有了雜湊索引,便會透過記憶體中的雜湊表來存取資料。With a hash index, data is accessed through an in-memory hash table. 雜湊索引會耗用固定數量的記憶體,也就是值區計數的函數。Hash indexes consume a fixed amount of memory, which is a function of the bucket count. 使用記憶體最佳化資料表索引的方針Guidelines for Using Indexes on Memory-Optimized Tables

雜湊索引設計指導方針Hash Index Design Guidelines
記憶體最佳化的非叢集memory-optimized Nonclustered 對於已完成記憶體最佳化的非叢集索引,記憶體耗用量是資料列計數和索引鍵資料行大小的函數For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns 使用記憶體最佳化資料表索引的方針Guidelines for Using Indexes on Memory-Optimized Tables

記憶體最佳化的非叢集索引設計指導方針Memory-Optimized Nonclustered Index Design Guidelines
叢集Clustered 索引叢集根據叢集索引鍵的順序來排序和儲存資料表或檢視的資料列。A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. 叢集索引將實作成 B 型樹狀索引結構,以根據它們的叢集索引鍵值快速地擷取資料列。The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. 叢集與非叢集索引說明Clustered and Nonclustered Indexes Described

建立叢集索引Create Clustered Indexes

叢集索引設計指導方針Clustered Index Design Guidelines
非叢集Nonclustered 非叢集索引可在具有叢集索引的資料表或檢視中、或是堆積中定義。A nonclustered index can be defined on a table or view with a clustered index or on a heap. 非叢集索引中的每個索引資料列都含有非叢集鍵值與資料列定位器。Each index row in the nonclustered index contains the nonclustered key value and a row locator. 此定位器指向含有鍵值之叢集索引或堆積中的資料列。This locator points to the data row in the clustered index or heap having the key value. 索引中的資料列會依據索引鍵值的順序儲存,但除非叢集索引建立在資料表中,否則資料列不一定會依循任何特定的順序排列。The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table. 叢集與非叢集索引說明Clustered and Nonclustered Indexes Described

建立非叢集索引Create Nonclustered Indexes

非叢集索引設計指導方針Nonclustered Index Design Guidelines
唯一Unique 唯一索引可確保索引鍵不含重複的值,因此資料表或檢視中的每個資料列就某方面而言都是唯一的。A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

唯一性可以是叢集與非叢集索引的屬性。Uniqueness can be a property of both clustered and nonclustered indexes.
建立唯一索引Create Unique Indexes

唯一索引設計指導方針Unique Index Design Guidelines
columnstoreColumnstore 記憶體中的資料行存放區索引會使用資料行為基礎的資料儲存和資料行為基礎的查詢處理來儲存及管理資料。An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.

資料行存放區索引可在主要執行大量載入和唯讀查詢的資料倉儲工作負載中順利運作。Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. 與傳統的資料列導向儲存相較之下,使用資料行存放區索引最高可達到 10 倍查詢效能 改善,與未壓縮資料大小相較之下,最高可達到 7 倍資料壓縮Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.
資料行存放區索引指南Columnstore Indexes Guide

資料行存放區索引設計指導方針Columnstore Index Design Guidelines
具有內含資料行的索引Index with included columns 除了索引鍵資料行以外,擴充為含有非索引鍵資料行的非叢集索引。A nonclustered index that is extended to include nonkey columns in addition to the key columns. 建立內含資料行的索引Create Indexes with Included Columns
導出資料行的索引Index on computed columns 從其他一個或多個資料行的值,或特定決定性輸入衍生的資料行索引。An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs. 計算資料行的索引Indexes on Computed Columns
已篩選Filtered 最佳化的非叢集索引,特別適合涵蓋從妥善定義的資料子集進行選取的查詢。An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. 篩選索引會使用篩選述詞對資料表中的部分資料列進行索引。It uses a filter predicate to index a portion of rows in the table. 與完整資料表索引相較,設計良好的篩選索引可以提升查詢效能、降低索引維護成本,並降低索引儲存成本。A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. 建立篩選的索引Create Filtered Indexes

篩選索引設計指導方針Filtered Index Design Guidelines
空間Spatial 空間索引可以更有效率地在 geometry 資料類型之資料行的空間物件 (「空間資料」 ) 上執行特定作業。A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. 空間索引會減少需要套用相當耗成本之空間作業的物件數目。The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied. 空間索引概觀Spatial Indexes Overview
XMLXML xml 資料類型資料行中,一種細分且持續的 XML 二進位大型物件 (BLOB) 表示法。A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column. XML 索引 (SQL Server)XML Indexes (SQL Server)
全文檢索Full-text 一種特殊類型的 Token 式功能索引,由 Microsoft Full-Text Engine for SQL ServerSQL Server所建立與維護。A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL ServerSQL Server. 它可以有效地在字元字串資料中進行複雜字的搜尋。It provides efficient support for sophisticated word searches in character string data. 擴展全文檢索索引Populate Full-Text Indexes

SQL Server 索引設計指南 SQL Server Index Design Guide
索引的 SORT_IN_TEMPDB 選項 SORT_IN_TEMPDB Option For Indexes
停用索引和條件約束 Disable Indexes and Constraints
啟用索引和條件約束 Enable Indexes and Constraints
重新命名索引 Rename Indexes
設定索引選項 Set Index Options
索引 DDL 作業的磁碟空間需求 Disk Space Requirements for Index DDL Operations
重新組織與重建索引 Reorganize and Rebuild Indexes
指定索引的填滿因素 Specify Fill Factor for an Index
分頁與範圍架構指南 Pages and Extents Architecture Guide
叢集與非叢集索引說明Clustered and Nonclustered Indexes Described