ALTER INDEX (Transact-SQL)ALTER INDEX (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

藉由停用、重建或重新組織索引或設定索引選項,修改現有的資料表或檢視表索引 (資料列存放區、資料行存放區或 XML)。Modifies an existing table or view index (rowstore, columnstore, or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server and Azure SQL Database
  
ALTER INDEX { index_name | ALL } ON <object>  
{  
      REBUILD {  
            [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]   
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]  
      }  
    | DISABLE  
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]  
    | SET ( <set_index_option> [ ,...n ] )   
    | RESUME [WITH (<resumable_index_options>,[...n])]
    | PAUSE
    | ABORT
}  
[ ; ]  
  
<object> ::=   
{  
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }  
}  
  
<rebuild_index_option > ::=  
{  
      PAD_INDEX = { ON | OFF }  
    | FILLFACTOR = fillfactor   
    | SORT_IN_TEMPDB = { ON | OFF }  
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | STATISTICS_INCREMENTAL = { ON | OFF }  
    | ONLINE = {   
          ON [ ( <low_priority_lock_wait> ) ]   
        | OFF } 
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | COMPRESSION_DELAY = {0 | delay [Minutes]}  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }   
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]  
}  
  
<single_partition_rebuild_index_option> ::=  
{  
      SORT_IN_TEMPDB = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }  
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }  
}  
  
<reorganize_option>::=  
{  
       LOB_COMPACTION = { ON | OFF }  
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}  
}  
  
<set_index_option>::=  
{  
      ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | COMPRESSION_DELAY= {0 | delay [Minutes]}  
}  

<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }
 
<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  
}  

-- Syntax for SQL Data Warehouse and Parallel Data Warehouse 
  
ALTER INDEX { index_name | ALL }  
    ON   [ schema_name. ] table_name  
{  
      REBUILD {  
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ] 
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> )] ] 
      }  
    | DISABLE  
    | REORGANIZE [ PARTITION = partition_number ]  
}  
[;]  

<rebuild_index_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]   
}

<single_partition_rebuild_index_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
}  
  

引數Arguments

index_nameindex_name
這是索引的名稱。Is the name of the index. 在資料表或檢視表內,索引名稱必須是唯一的,但在資料庫內就不一定要是唯一的。Index names must be unique within a table or view but do not have to be unique within a database. 索引名稱必須遵照識別碼的規則。Index names must follow the rules of identifiers.

ALLALL
指定與資料表或檢視表相關聯的所有索引 (不論索引類型為何)。Specifies all indexes associated with the table or view regardless of the index type. 如果有一個或多個索引在離線或唯讀檔案群組中,或有一個或多個索引類型不允許指定的作業,指定 ALL 便會使陳述式失敗。Specifying ALL causes the statement to fail if one or more indexes are in an offline or read-only filegroup or the specified operation is not allowed on one or more index types. 下表列出索引作業和不允許的索引類型。The following table lists the index operations and disallowed index types.

搭配此作業使用關鍵字 ALLUsing the keyword ALL with this operation 如果資料表有一個或多個下列項目,便告失敗Fails if the table has one or more
REBUILD WITH ONLINE = ONREBUILD WITH ONLINE = ON XML 索引XML index

空間索引Spatial index

資料行存放區索引:適用範圍: SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseColumnstore index: Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database
REBUILD PARTITION = partition_numberREBUILD PARTITION = partition_number 未分割索引、XML 索引、空間索引或停用的索引Nonpartitioned index, XML index, spatial index, or disabled index
REORGANIZEREORGANIZE ALLOW_PAGE_LOCKS 設定為 OFF 的索引Indexes with ALLOW_PAGE_LOCKS set to OFF
REORGANIZE PARTITION = partition_numberREORGANIZE PARTITION = partition_number 未分割索引、XML 索引、空間索引或停用的索引Nonpartitioned index, XML index, spatial index, or disabled index
IGNORE_DUP_KEY = ONIGNORE_DUP_KEY = ON XML 索引XML index

空間索引Spatial index

資料行存放區索引:適用範圍: SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseColumnstore index: Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database
ONLINE = ONONLINE = ON XML 索引XML index

空間索引Spatial index

資料行存放區索引:適用範圍: SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseColumnstore index: Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database
RESUMABLE = ONRESUMABLE = ON All 關鍵字不支援可繼續的索引。Resumable indexes not supported with All keyword.

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

警告

如需有關可以在線上執行之索引作業的詳細資訊,請參閱線上索引作業的指導方針For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

如果設定 PARTITION = partition_number來指定 ALL,便會對齊所有索引。If ALL is specified with PARTITION = partition_number, all indexes must be aligned. 這表示它們會根據對等的分割區函數來進行分割。This means that they are partitioned based on equivalent partition functions. 搭配 PARTITION 子句使用 ALL 時,會重建或重新組織含有相同 partition_number 的所有索引分割區。Using ALL with PARTITION causes all index partitions with the same partition_number to be rebuilt or reorganized. 如需有關分割區索引的詳細資訊,請參閱< Partitioned Tables and Indexes>。For more information about partitioned indexes, see Partitioned Tables and Indexes.

database_namedatabase_name
這是資料庫的名稱。Is the name of the database.

schema_nameschema_name
這是資料表或檢視表所屬的結構描述名稱。Is the name of the schema to which the table or view belongs.

table_or_view_nametable_or_view_name
這是與索引相關聯的資料表或檢視表的名稱。Is the name of the table or view associated with the index. 若要顯示物件的索引報表,請使用 sys.indexes 目錄檢視。To display a report of the indexes on an object, use the sys.indexes catalog view.

當 database_name 是目前的資料庫或 database_name 是 tempdb,而且 table_or_view_name 開頭為 # 時,SQL DatabaseSQL Database 支援三部分名稱格式 database_name.[schema_name].table_or_view_name。SQL DatabaseSQL Database supports the three-part name format database_name.[schema_name].table_or_view_name when the database_name is the current database or the database_name is tempdb and the table_or_view_name starts with #.

REBUILD [ WITH ( <rebuild_index_option> [ , ... n] ) ]REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]

適用於SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database

指定將利用相同的資料行、索引類型、唯一性屬性和排序次序來重建索引。Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. 這個子句相當於 DBCC DBREINDEXThis clause is equivalent to DBCC DBREINDEX. REBUILD 會啟用停用的索引。REBUILD enables a disabled index. 除非指定了 ALL 關鍵字,否則重建叢集索引不會重建相關聯的非叢集索引。Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. 如果未指定索引選項,便會套用儲存在 sys.indexes 中的現有索引選項值。If index options are not specified, the existing index option values stored in sys.indexes are applied. 任何值未儲存在 sys.indexes 中的索引選項,都會套用選項引數定義中所指示的預設值。For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

如果指定了 ALL,且基礎資料表是堆積,重建作業便不會影響資料表。If ALL is specified and the underlying table is a heap, the rebuild operation has no effect on the table. 與資料表相關聯的任何非叢集索引都會重建。Any nonclustered indexes associated with the table are rebuilt.

如果資料庫復原模式設為大量記錄模式或簡單模式,重建作業便可以只進行最基本的記錄。The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

注意

當您重建主要 XML 索引時,在索引作業的持續時間,無法使用基礎使用者資料表。When you rebuild a primary XML index, the underlying user table is unavailable for the duration of the index operation.

對於資料行存放區索引,重建作業:For columnstore indexes, the rebuild operation:

  • 不使用排序次序。Does not use the sort order.
  • 在進行重建時,取得資料表或分割區上的獨佔鎖定。Acquires an exclusive lock on the table or partition while the rebuild occurs. 在重建期間,資料處於「離線」狀態而且無法使用,即使使用 NOLOCK、RCSI 或 SI 亦然。The data is "offline" and unavailable during the rebuild, even when using NOLOCK, RCSI, or SI.
  • 將所有資料重新壓縮到資料行存放區。Re-compresses all data into the columnstore. 當重建進行時,有兩個資料行存放區索引複本存在。Two copies of the columnstore index exist while the rebuild is taking place. 當重建完成時, SQL ServerSQL Server 會刪除原始資料行存放區索引。When the rebuild is finished, SQL ServerSQL Server deletes the original columnstore index.

如需詳細資訊,請參閱 重新組織與重建索引For more information, see Reorganize and Rebuild Indexes.

PARTITIONPARTITION

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定只重建或重新組織索引的一個分割區。Specifies that only one partition of an index will be rebuilt or reorganized. 如果 index_name 不是分割區索引,便不能指定 PARTITION。PARTITION cannot be specified if index_name is not a partitioned index.

PARTITION = ALL 會重建所有分割區。PARTITION = ALL rebuilds all partitions.

警告

您可以對包含超過 1,000 個分割區的資料表,建立及重建不以資料表為準的索引,但不予支援。Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. 此做法可能會導致在作業期間效能降低或耗用過多記憶體。Doing so may cause degraded performance or excessive memory consumption during these operations. Microsoft 建議當分割區數超過 1,000 個時,才使用以資料表為準的索引。Microsoft recommends using only aligned indexes when the number of partitions exceed 1,000.

partition_numberpartition_number

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

要重建或重新組織之分割區索引的分割區數。Is the partition number of a partitioned index that is to be rebuilt or reorganized. partition_number 是一個可以參考變數的常數運算式。partition_number is a constant expression that can reference variables. 其中包括使用者定義類型變數或函數及使用者定義函數,但無法參考 Transact-SQLTransact-SQL 陳述式。These include user-defined type variables or functions and user-defined functions, but cannot reference a Transact-SQLTransact-SQL statement. partition_number必須存在,否則陳述式將會失敗。partition_number must exist or the statement fails.

WITH ( <single_partition_rebuild_index_option> )WITH (<single_partition_rebuild_index_option>)

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

SORT_IN_TEMPDBMAXDOPDATA_COMPRESSION 是重建單一分割區 (PARTITION = partition_number) 時所能指定的選項。SORT_IN_TEMPDB, MAXDOP, and DATA_COMPRESSION are the options that can be specified when you rebuild a single partition (PARTITION = partition_number). 不能在單一分割區重建作業中指定 XML 索引。XML indexes cannot be specified in a single partition rebuild operation.

DISABLEDISABLE
將索引標示為已停用,無法供 Database EngineDatabase Engine 使用。Marks the index as disabled and unavailable for use by the Database EngineDatabase Engine. 任何索引都可以停用。Any index can be disabled. 已停用之索引的索引定義會保留在系統目錄中,但不含基礎索引資料。The index definition of a disabled index remains in the system catalog with no underlying index data. 停用叢集索引可以防止使用者存取基礎資料表資料。Disabling a clustered index prevents user access to the underlying table data. 若要啟用索引,請使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING. 如需詳細資訊,請參閱停用索引和條件約束啟用索引與條件約束For more information, see Disable Indexes and Constraints and Enable Indexes and Constraints.

REORGANIZE 資料列存放區索引REORGANIZE a rowstore index
對於資料列存放區索引,REORGANIZE 會指定重新組織索引分葉層級。For rowstore indexes, REORGANIZE specifies to reorganize the index leaf level. REORGANIZE 作業:The REORGANIZE operation is:

  • 一律在線上執行。Always performed online. 這表示不會保留長期封鎖的資料表鎖定,而且在 ALTER INDEX REORGANIZE 交易期間,可以繼續查詢或更新基礎資料表。This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
  • 不允許停用的索引使用Not allowed for a disabled index
  • 在 ALLOW_PAGE_LOCKS 設為「關閉」時,不允許使用Not allowed when ALLOW_PAGE_LOCKS is set to OFF
  • 在交易中執行且回復交易時不會回復。Not rolled back when it is performed within a transaction and the transaction is rolled back.

如需詳細資訊,請參閱 重新組織與重建索引For more information, see Reorganize and Rebuild Indexes.

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )
適用於資料列存放區索引。Applies to rowstore indexes.

LOB_COMPACTION = ONLOB_COMPACTION = ON

  • 指定壓縮包含下列大型物件 (LOB) 資料類型資料的所有頁面:image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。Specifies to compact all pages that contain data of these large object (LOB) data types: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. 壓縮此資料可縮小磁碟上的資料大小。Compacting this data can reduce the data size on disk.
  • 對於叢集索引,這會壓縮資料表中包含的所有 LOB 資料行。For a clustered index, this compacts all LOB columns that are contained in the table.
  • 對於非叢集索引,這會壓縮索引中本身是非索引鍵資料行 (內含資料行) 的所有 LOB 資料行。For a nonclustered index, this compacts all LOB columns that are nonkey (included) columns in the index.
  • REORGANIZE ALL 會在所有索引上執行 LOB_COMPACTION。REORGANIZE ALL performs LOB_COMPACTION on all indexes. 對於每個索引,這會壓縮叢集索引中的所有 LOB 資料行、基礎資料表,或非叢集索引中包含的資料行。For each index, this compacts all LOB columns in the clustered index, underlying table, or included columns in a nonclustered index.

LOB_COMPACTION = OFFLOB_COMPACTION = OFF

  • 不壓縮包含大型物件資料的頁面。Pages that contain large object data are not compacted.
  • OFF 對堆積沒有作用。OFF has no effect on a heap.

REORGANIZE 資料行存放區索引REORGANIZE a columnstore index
對於資料行存放區索引,REORGANIZE 會以壓縮的資料列群組方式,將每個 CLOSED 的差異資料列群組壓縮至資料行存放區中。For columnstore indexes, REORGANIZE compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. REORGANIZE 作業一律會在線上執行。The REORGANIZE operation is always performed online. 這表示不會保留長期封鎖的資料表鎖定,而且在 ALTER INDEX REORGANIZE 交易期間,可以繼續查詢或更新基礎資料表。This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. 如需詳細資訊,請參閱 重新組織與重建索引For more information, see Reorganize and Rebuild Indexes.

  • 不需要使用 REORGANIZE,也能將關閉的差異資料列群組移到壓縮的資料列群組中。REORGANIZE is not required in order to move CLOSED delta rowgroups into compressed rowgroups. 背景 tuple-mover (TM) 流程會定期喚醒,以壓縮關閉的差異資料列群組。The background tuple-mover (TM) process wakes up periodically to compress CLOSED delta rowgroups. 當 tuple-mover 進度落後時,我們建議使用 REORGANIZE。We recommend using REORGANIZE when tuple-mover is falling behind. REORGANIZE 可以更積極地壓縮資料列群組。REORGANIZE can compress rowgroups more aggressively.
  • 若要壓縮所有 OPEN 和 CLOSED 的資料列群組,請參閱本節中的 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) 選項。To compress all OPEN and CLOSED rowgroups, see the REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) option in this section.

對於 SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始) 和 Azure SQL DatabaseAzure SQL Database 中的資料行存放區索引,REORGANIZE 會線上執行以下額外的重組最佳化:For columnstore indexes in SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database, REORGANIZE performs the following additional defragmentation optimizations online:

  • 當 10% 或更多資料列已經以邏輯方式刪除時,會實際將資料列從資料列群組移除。Physically removes rows from a rowgroup when 10% or more of the rows have been logically deleted. 已刪除的位元組會在實體媒體上回收。The deleted bytes are reclaimed on the physical media. 例如,如果在包含 1 百萬個資料列的壓縮資料列群組中刪除 10 萬個資料列,SQL Server 將會移除刪除的資料列,並重新壓縮包含 90 萬個資料列的資料列群組。For example, if a compressed row group of 1 million rows has 100K rows deleted, SQL Server will remove the deleted rows and recompress the rowgroup with 900k rows. 將刪除的資料列移除可以節省儲存空間。It saves on the storage by removing deleted rows.

  • 可合併一或多個壓縮的資料列群組,將每個資料列群組的資料列數目最多提高至 1,024,576 個資料列的數目上限。Combines one or more compressed rowgroups to increase rows per rowgroup up to the maximum of 1,024,576 rows. 例如,如果您大量匯入 5 個批次的 102,400 個資料列,就會有 5 個壓縮的資料列群組。For example, if you bulk import 5 batches of 102,400 rows you will get 5 compressed rowgroups. 如果執行 REORGANIZE,這些資料列群組將會合併成 1 個包含 512,000 個資料列的壓縮資料列群組。If you run REORGANIZE, these rowgroups will get merged into 1 compressed rowgroup of size 512,000 rows. 這是假設沒有任何目錄大小或記憶體限制的情況。This assumes there were no dictionary size or memory limitations.

  • 對於已透過邏輯方式刪除 10% 或更多資料列的資料列群組,SQL Server 將會嘗試把這個資料列群組和一或多個資料列群組合併。For rowgroups in which 10% or more of the rows have been logically deleted, SQL Server will try to combine this rowgroup with one or more rowgroups. 例如,資料列群組 1 壓縮了 500,000 個資料列,而資料列群組 21 則壓縮了達到數目上限的 1,048,576 個資料列。For example, rowgroup 1 is compressed with 500,000 rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows. 資料列群組 21 中刪除了 60% 的資料列,剩下 409,830 個資料列。Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. SQL Server 會合併這兩個資料列群組,以壓縮一個包含 909,830 個資料列的新資料列群組。SQL Server favors combining these two rowgroups to compress a new rowgroup that has 909,830 rows.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )
適用資於料行存放區索引。Applies to columnstore indexes.

適用範圍: SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database

COMPRESS_ALL_ROW_GROUPS 可用來將開啟或關閉的差異資料列群組強制移動到資料行存放區中。COMPRESS_ALL_ROW_GROUPS provides a way to force OPEN or CLOSED delta rowgroups into the columnstore. 使用此選項時,不需要重建資料行存放區索引來清空差異資料列群組。With this option, it is not necessary to rebuild the columnstore index to empty the delta rowgroups. 此功能和其他移除與合併重組功能結合之後,可讓它在大部分情況下都不再需要重建索引。This, combined with the other remove and merge defragmentation features makes it no longer necessary to rebuild the index in most situations.

  • ON 會將所有資料列群組強制移動到資料行存放區中,無論其大小和狀態 (關閉或開啟) 為何。ON forces all rowgroups into the columnstore, regardless of size and state (CLOSED or OPEN).
  • OFF 則會將所有關閉的資料列群組強制移動到資料行存放區中。OFF forces all CLOSED rowgroups into the columnstore.

如需詳細資訊,請參閱 重新組織與重建索引For more information, see Reorganize and Rebuild Indexes.

SET ( <set_index option> [ , ... n] )SET ( <set_index option> [ ,... n] )
在不重建或重新組織索引的情況下,指定索引選項。Specifies index options without rebuilding or reorganizing the index. 停用的索引不能指定 SET。SET cannot be specified for a disabled index.

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定索引填補。Specifies index padding. 預設值為 OFF。The default is OFF.

開啟ON
FILLFACTOR 指定的可用空間百分比會套用到索引的中繼層級頁面上。The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index. 如果 PAD_INDEX 設為 ON 時,並未指定 FILLFACTOR,就會使用 sys.indexes中所儲存的填滿因數值。If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.

OFF 或未指定 fillfactorOFF or fillfactor is not specified
填入中繼層級頁面至接近填滿的程度。The intermediate-level pages are filled to near capacity. 這會保留至少足以容納一個資料列的空間,且該資料列具有索引所能擁有的大小上限 (以中繼頁面的索引鍵組為基礎)。This leaves sufficient space for at least one row of the maximum size that the index can have, based on the set of keys on the intermediate pages.

如需詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).

FILLFACTOR = fillfactorFILLFACTOR = fillfactor

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定一個百分比來指出在建立或改變索引期間,Database EngineDatabase Engine 應該使各索引頁面之分葉層級填滿的程度。Specifies a percentage that indicates how full the Database EngineDatabase Engine should make the leaf level of each index page during index creation or alteration. fillfactor 必須是 1 到 100 之間的整數值。fillfactor must be an integer value from 1 to 100. 預設值是 0。The default is 0. 填滿因數值 0 和 100 在各方面都是一樣的。Fill factor values 0 and 100 are the same in all respects.

只有在最初建立或重建索引時,才適用明確的 FILLFACTOR 設定。An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. Database EngineDatabase Engine 不會動態保留頁面中空白空間的指定百分比。The Database EngineDatabase Engine does not dynamically keep the specified percentage of empty space in the pages. 如需詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).

若要檢視填滿因數設定,請使用 sys.indexesTo view the fill factor setting, use sys.indexes.

重要

利用 FILLFACTOR 值來建立或變更叢集索引時,會影響資料所佔用的儲存空間量,因為 Database EngineDatabase Engine 在建立叢集索引時,會轉散發資料。Creating or altering a clustered index with a FILLFACTOR value affects the amount of storage space the data occupies, because the Database EngineDatabase Engine redistributes the data when it creates the clustered index.

SORT_IN_TEMPDB = { ON | OFF }SORT_IN_TEMPDB = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定是否將排序結果儲存在 tempdb 中。Specifies whether to store the sort results in tempdb. 預設值是關閉,但 Azure SQL Database 超大規模資料庫例外。The default is OFF except for Azure SQL Database Hyperscale. 針對超大規模資料庫中的所有索引重建作業,不論指定的選項為何,除非使用可繼續的索引重建,否則 SORT_IN_TEMPDB 一律會是 ON。For all index rebuild operations in Hyperscale, SORT_IN_TEMPDB is always ON, regardless of the option specified unless resumable index rebuild is used.

開啟ON
用來建置索引的中繼排序結果會儲存在 tempdb 中。The intermediate sort results that are used to build the index are stored in tempdb. 如果 tempdb 是在使用者資料庫以外的磁碟組中,這可能會縮短建立索引所需要的時間。If tempdb is on a different set of disks than the user database, this may reduce the time needed to create an index. 不過,這會增加建立索引時所使用的磁碟空間量。However, this increases the amount of disk space that is used during the index build.

OFFOFF
中繼排序結果會儲存在與用來儲存索引相同的資料庫中。The intermediate sort results are stored in the same database as the index.

如果不需要排序作業,或者可以在記憶體中執行排序,則忽略 SORT_IN_TEMPDB 選項。If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

如需詳細資訊,請參閱索引的 SORT_IN_TEMPDB 選項For more information, see SORT_IN_TEMPDB Option For Indexes.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 預設值為 OFF。The default is OFF.

開啟ON
當重複的索引鍵值插入唯一索引時,就會出現警告訊息。A warning message will occur when duplicate key values are inserted into a unique index. 只有違反唯一性條件約束的資料列才會失敗。Only the rows violating the uniqueness constraint will fail.

OFFOFF
當重複的索引鍵值插入唯一索引時,就會出現錯誤訊息。An error message will occur when duplicate key values are inserted into a unique index. 整個 INSERT 作業將會回復。The entire INSERT operation will be rolled back.

若為針對檢視表所建立的索引、非唯一索引、XML 索引、空間索引和篩選索引,IGNORE_DUP_KEY 不得設為 ON。IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

若要檢視 IGNORE_DUP_KEY,請使用 sys.indexesTo view IGNORE_DUP_KEY, use sys.indexes.

在與舊版本相容的語法中,WITH IGNORE_DUP_KEY 相當於 WITH IGNORE_DUP_KEY = ON。In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否要重新計算散發統計資料。Specifies whether distribution statistics are recomputed. 預設值為 OFF。The default is OFF.

開啟ON
不會自動重新計算過期的統計資料。Out-of-date statistics are not automatically recomputed.

OFFOFF
啟用自動統計資料更新。Automatic statistics updating are enabled.

若要還原自動統計資料更新,請將 STATISTICS_NORECOMPUTE 設為 OFF,或執行不含 NORECOMPUTE 子句的 UPDATE STATISTICS。To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

重要

停用散發統計資料的自動重新計算,可防止查詢最佳化工具取得與資料表有關之查詢的最佳執行計畫。Disabling automatic recomputation of distribution statistics may prevent the Query Optimizer from picking optimal execution plans for queries that involve the table.

STATISTICS_INCREMENTAL = { ON | OFF }STATISTICS_INCREMENTAL = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

若設定為 ON,所建立的統計資料會以每個資料分割統計資料為依據。When ON, the statistics created are per partition statistics. 若為 OFF,則會卸除統計資料樹狀結構,且 SQL ServerSQL Server 會重新計算統計資料。When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. 預設值為 OFFThe default is OFF.

如果不支援每個分割區區的統計資料,則會忽略該選項,並產生警告。If per partition statistics are not supported the option is ignored and a warning is generated. 針對下列統計資料類型,不支援累加統計資料:Incremental stats are not supported for following statistics types:

  • 建立統計資料時,所使用的索引未與基底資料表進行分割區對齊Statistics created with indexes that are not partition-aligned with the base table
  • 在 Always On 可讀取次要資料庫上建立的統計資料Statistics created on Always On readable secondary databases
  • 在唯讀資料庫上建立的統計資料Statistics created on read-only databases
  • 在篩選的索引上建立的統計資料Statistics created on filtered indexes
  • 在檢視表上建立的統計資料Statistics created on views
  • 在內部資料表上建立的統計資料Statistics created on internal tables
  • 使用空間索引或 XML 索引建立的統計資料Statistics created with spatial indexes or XML indexes

ONLINE = { ON | OFF } <在套用至 rebuild_index_option 時>ONLINE = { ON | OFF } <as applies to rebuild_index_option>
指定在索引作業期間,查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. 預設值為 OFF。The default is OFF.

如果是 XML 索引或空間索引,則只支援 ONLINE = OFF,而如果將 ONLINE 設定為 ON,將會引發錯誤。For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error is raised.

重要

並非所有版本的 MicrosoftMicrosoft SQL ServerSQL Server 都可以使用線上索引作業。Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 版本和支援的功能SQL Server 2017 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016 (13.x)SQL Server 2016 (13.x) and Editions and Supported Features for SQL Server 2017.

開啟ON
索引作業持續期間不會保留長期資料表鎖定。Long-term table locks are not held for the duration of the index operation. 在索引作業的主要階段期間,來源資料表上只保留意圖共用 (IS) 鎖定。During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. 這使得基礎資料表和索引的查詢或更新能夠繼續運作。This allows queries or updates to the underlying table and indexes to continue. 在作業開始時,共用 (S) 鎖定會在來源物件上保留一段很短的時間。At the start of the operation, a Shared (S) lock is very briefly held on the source object. 在作業結束時,如果建立非叢集索引,S (共用) 鎖定會在來源上保留一段很短的時間;在線上建立或卸除叢集索引時,以及重建叢集或非叢集索引時,將會取得 SCH-M (結構描述修改) 鎖定。At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. 建立本機暫存資料表的索引時,ONLINE 不可設為 ON。ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFFOFF
在索引作業期間會套用資料表鎖定。Table locks are applied for the duration of the index operation. 建立、重建或卸除叢集索引、空間索引或 XML 索引的離線索引作業,或是重建或卸除非叢集索引的離線索引作業,將會取得資料表的結構描述修改 (Sch-M) 鎖定。An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. 這可防止所有使用者在作業持續期間存取基礎資料表。This prevents all user access to the underlying table for the duration of the operation. 建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. 這可避免對基礎資料表進行更新,但仍可執行讀取作業,如 SELECT 陳述式。This prevents updates to the underlying table but allows read operations, such as SELECT statements.

如需詳細資訊,請參閱 Perform Index Operations OnlineFor more information, see Perform Index Operations Online.

您可以在線上重建索引,其中包括全域暫存資料表的索引,但下列情況除外:Indexes, including indexes on global temp tables, can be rebuilt online except for the following cases:

  • XML 索引XML index
  • 本機暫存資料表上的索引Index on a local temp table
  • 在檢視上的初始唯一叢集索引Initial unique clustered index on a view
  • 資料行存放區索引Columnstore indexes
  • 叢集索引 (如果基礎資料表包含 LOB 資料類型 (imagentexttext) 及空間類型)Clustered index, if the underlying table contains LOB data types (image, ntext, text) and spatial data types
  • varchar(max)varbinary(max) 資料行不得為索引的一部分。varchar(max) and varbinary(max) columns cannot be part of an index. SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始) 和 Azure SQL DatabaseAzure SQL Database 中,當資料表包含 varchar(max)varbinary(max) 資料行時,可以使用 ONLINE 選項來建置或重建包含其他資料行的叢集索引。In SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)) and Azure SQL DatabaseAzure SQL Database, when a table contains varchar(max) or varbinary(max) columns, a clustered index containing other columns can be built or rebuilt using the ONLINE option. 當基底資料表包含 varchar(max)varbinary(max) 資料行時,Azure SQL DatabaseAzure SQL Database 不允許 ONLINE 選項Azure SQL DatabaseAzure SQL Database does not permit the ONLINE option when the base table contains varchar(max) or varbinary(max) columns

如需詳細資訊,請參閱線上索引作業如何運作For more information, see How Online Index Operations Work.

RESUMABLE = { ON | OFF}RESUMABLE = { ON | OFF}

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

指定線上索引作業是否為可繼續的作業。Specifies whether an online index operation is resumable.

ON 索引作業為可繼續的作業。ON Index operation is resumable.

OFF 索引作業不是可繼續的作業。OFF Index operation is not resumable.

MAX_DURATION = time [MINUTES] 與 RESUMABLE = ON 搭配使用 (需要 ONLINE = ON)。MAX_DURATION = time [MINUTES] used with RESUMABLE = ON (requires ONLINE = ON).

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

指出可繼續的線上索引作業在暫停之前的執行時間 (以分鐘為單位指定的一個整數值)。Indicates time (an integer value specified in minutes) that a resumable online index operation is executed before being paused.

重要

如需有關可以在線上執行之索引作業的詳細資訊,請參閱線上索引作業的指導方針For more detailed information about index operations that can be performed online, see Guidelines for Online Index Operations.

注意

資料行存放區索引不支援可繼續的線上索引重建。Resumable online index rebuilds are not supported on columnstore indexes.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定是否允許資料列鎖定。Specifies whether row locks are allowed. 預設值是 ON。The default is ON.

開啟ON
當存取索引時,允許資料列鎖定。Row locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用資料列鎖定的時機。The Database EngineDatabase Engine determines when row locks are used.

OFFOFF
不使用資料列鎖定。Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定是否允許頁面鎖定。Specifies whether page locks are allowed. 預設值是 ON。The default is ON.

開啟ON
當您存取索引時,允許頁面鎖定。Page locks are allowed when you access the index. Database EngineDatabase Engine 會決定使用頁面鎖定的時機。The Database EngineDatabase Engine determines when page locks are used.

OFFOFF
不使用頁面鎖定。Page locks are not used.

注意

當 ALLOW_PAGE_LOCKS 設為 OFF 時,無法重新組織索引。An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用於SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and Azure SQL DatabaseAzure SQL Database

指定是否要最佳化最後一頁的插入競爭。Specifies whether or not to optimize for last-page insert contention. 預設值為 OFF。The default is OFF. 請參閱 CREATE INDEX 頁面的循序索引鍵一節以取得詳細資訊。See the Sequential Keys section of the CREATE INDEX page for more information.

MAXDOP = max_degree_of_parallelismMAXDOP = max_degree_of_parallelism

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

在索引作業期間,覆寫 max degree of parallelism 設定選項。Overrides the max degree of parallelism configuration option for the duration of the index operation. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option. 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

重要

雖然所有 XML 索引在語法上都支援 MAXDOP 選項,但是對於空間索引或主要 XML 索引而言,ALTER INDEX 目前只會使用單一處理器。Although the MAXDOP option is syntactically supported for all XML indexes, for a spatial index or a primary XML index, ALTER INDEX currently uses only a single processor.

max_degree_of_parallelism 可以是:max_degree_of_parallelism can be:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>1
將平行索引作業所用的最大處理器數目限制為指定的數目。Restricts the maximum number of processors used in a parallel index operation to the specified number.

0 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

注意

SQL ServerSQL Server 的所有版本都無法使用平行索引作業。Parallel index operations are not available in every edition of SQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

COMPRESSION_DELAY = { 0 |持續時間 [分鐘] }COMPRESSION_DELAY = { 0 |duration [Minutes] }

適用範圍: SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x))

至於磁碟資料表,延遲會指定關閉 狀態下的差異資料列群組,必須在差異資料列群組中至少保留多少分鐘的時間,然後 SQL Server 才能將它壓縮到壓縮的資料列群組。For a disk-based table, delay specifies the minimum number of minutes a delta rowgroup in the CLOSED state must remain in the delta rowgroup before SQL Server can compress it into the compressed rowgroup. 因為磁碟資料表不會追蹤個別資料列的插入和更新時間,因此 SQL Server 會將這段延遲時間套用於關閉狀態下的差異資料列群組。Since disk-based tables don't track insert and update times on individual rows, SQL Server applies the delay to delta rowgroups in the CLOSED state.
預設值是 0 分鐘。The default is 0 minutes.

預設值是 0 分鐘。The default is 0 minutes.

如需 COMPRESSION_DELAY 的使用時機建議,請參閱開始使用資料行存放區進行即時作業分析For recommendations on when to use COMPRESSION_DELAY, see Get started with Columnstore for real time operational analytics.

DATA_COMPRESSIONDATA_COMPRESSION
針對指定的索引、分割區編號或分割區範圍指定資料壓縮選項。Specifies the data compression option for the specified index, partition number, or range of partitions. 選項如下:The options are as follows:

NONE
不壓縮索引或指定的分割區。Index or specified partitions are not compressed. 這不適用於資料行存放區索引。This does not apply to columnstore indexes.

ROWROW
使用資料列壓縮來壓縮索引或指定的分割區。Index or specified partitions are compressed by using row compression. 這不適用於資料行存放區索引。This does not apply to columnstore indexes.

PAGEPAGE
使用頁面壓縮來壓縮索引或指定的分割區。Index or specified partitions are compressed by using page compression. 這不適用於資料行存放區索引。This does not apply to columnstore indexes.

COLUMNSTORECOLUMNSTORE

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

只適用於資料行存放區索引,包括非叢集資料行存放區索引和叢集資料行存放區索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE 會指定解壓縮以 COLUMNSTORE_ARCHIVE 選項壓縮的索引或指定的分割區。COLUMNSTORE specifies to decompress the index or specified partitions that are compressed with the COLUMNSTORE_ARCHIVE option. 當還原資料時,資料將會繼續以用於所有資料行存放區索引的資料行存放區壓縮來壓縮。When the data is restored, it will continue to be compressed with the columnstore compression that is used for all columnstore indexes.

COLUMNSTORE_ARCHIVECOLUMNSTORE_ARCHIVE

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

只適用於資料行存放區索引,包括非叢集資料行存放區索引和叢集資料行存放區索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. COLUMNSTORE_ARCHIVE 將進一步將指定的分割區壓縮成較小的大小。COLUMNSTORE_ARCHIVE will further compress the specified partition to a smaller size. 這可用於封存,或是其他需要較小儲存體,而且可負擔更多時間來儲存和擷取的狀況。This can be used for archival, or for other situations that require a smaller storage size and can afford more time for storage and retrieval.

如需與壓縮有關的詳細資訊,請參閱資料壓縮For more information about compression, see Data Compression.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n] )ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

指定套用 DATA_COMPRESSION 設定的分割區。Specifies the partitions to which the DATA_COMPRESSION setting applies. 如果未分割此索引,ON PARTITIONS 引數將會產生錯誤。If the index is not partitioned, the ON PARTITIONS argument will generate an error. 如果未提供 ON PARTITIONS 子句,DATA_COMPRESSION 選項會套用到分割區索引的所有分割區。If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option applies to all partitions of a partitioned index.

可以使用以下方式來指定 <partition_number_expression>:<partition_number_expression> can be specified in the following ways:

  • 提供分割區的編號,例如:ON PARTITIONS (2)Provide the number for a partition, for example: ON PARTITIONS (2).
  • 為數個個別分割區提供以逗號分隔的分割區編號,例如:ON PARTITIONS (1, 5)Provide the partition numbers for several individual partitions separated by commas, for example: ON PARTITIONS (1, 5).
  • 同時提供範圍和個別分割區:ON PARTITIONS (2, 4, 6 TO 8)Provide both ranges and individual partitions: ON PARTITIONS (2, 4, 6 TO 8).

<range> 可以指定為以 TO 一字分隔的分割區編號,例如:ON PARTITIONS (6 TO 8)<range> can be specified as partition numbers separated by the word TO, for example: ON PARTITIONS (6 TO 8).

若要為不同的分割區設定不同類型的資料壓縮,請指定 DATA_COMPRESSION 選項一次以上,例如:To set different types of data compression for different partitions, specify the DATA_COMPRESSION option more than once, for example:

REBUILD WITH   
(  
DATA_COMPRESSION = NONE ON PARTITIONS (1),   
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),   
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)  
);  

ONLINE = { ON | OFF } <在套用至 single_partition_rebuild_index_option 時>ONLINE = { ON | OFF } <as applies to single_partition_rebuild_index_option>
指定基礎資料表的索引或索引分割區可以在線上重建或離線重建。Specifies whether an index or an index partition of an underlying table can be rebuilt online or offline. 如果 REBUILD 是在線上執行 (ON),這個資料表中的資料可以在索引作業期間用於查詢和資料修改。If REBUILD is performed online (ON) the data in this table is available for queries and data modification during the index operation. 預設值為 OFFThe default is OFF.

開啟ON
索引作業持續期間不會保留長期資料表鎖定。Long-term table locks are not held for the duration of the index operation. 在索引作業的主要階段期間,來源資料表上只保留意圖共用 (IS) 鎖定。During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. 在索引重建開始時,資料表上需要 S 鎖定,而在線上索引重建結束時,資料表上需要 Sch-M 鎖定。An S-lock on the table is required in the Starting of the index rebuild and a Sch-M lock on the table at the end of the online index rebuild. 雖然這兩個鎖定是短暫的中繼資料鎖定,尤其是 Sch-M 鎖定必須等候所有封鎖交易完成。Although both locks are short metadata locks, especially the Sch-M lock must wait for all blocking transactions to be completed. 在等候期間,Sch-M 鎖定會在存取相同資料表時,封鎖等待在這個鎖定之後的所有其他交易。During the wait time the Sch-M lock blocks all other transactions that wait behind this lock when accessing the same table.

注意

線上索引重建可設定本節稍後所述的 low_priority_lock_wait 選項。Online index rebuild can set the low_priority_lock_wait options described later in this section.

OFFOFF
在索引作業期間會套用資料表鎖定。Table locks are applied for the duration of the index operation. 這可防止所有使用者在作業持續期間存取基礎資料表。This prevents all user access to the underlying table for the duration of the operation.

WAIT_AT_LOW_PRIORITY 只能與 ONLINE=ON 搭配使用。WAIT_AT_LOW_PRIORITY used with ONLINE=ON only.

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

線上索引重建必須等候這個資料表的封鎖作業。An online index rebuild has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY 表示線上索引重建作業將會等候低優先順序鎖定,讓其他作業在線上索引建立作業等候時繼續進行。WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. 省略 WAIT AT LOW PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). 如需詳細資訊,請參閱 WAIT_AT_LOW_PRIORITYFor more information, see WAIT_AT_LOW_PRIORITY.

MAX_DURATION = time [MINUTES]MAX_DURATION = time [MINUTES]

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

執行 DDL 命令時,線上索引重建鎖定將會以低優先權等候的等候時間 (以分鐘為單位指定的整數值)。The wait time (an integer value specified in minutes) that the online index rebuild locks will wait with low priority when executing the DDL command. 如果作業被封鎖時間長度達到在 MAX_DURATION 中指定的時間,則會執行其中一個 ABORT_AFTER_WAIT 動作。If the operation is blocked for the MAX_DURATION time, one of the ABORT_AFTER_WAIT actions will be executed. MAX_DURATION 時間一律以分鐘為單位,而且可以省略 MINUTES 這個字。MAX_DURATION time is always in minutes, and the word MINUTES can be omitted.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

NONE
繼續等候一般 (標準) 優先權的鎖定。Continue waiting for the lock with normal (regular) priority.

SELFSELF
結束目前正在執行的線上索引重建 DDL 作業,但不採取任何動作。Exit the online index rebuild DDL operation currently being executed without taking any action.

BLOCKERSBLOCKERS
終止封鎖線上索引重建 DDL 作業的所有使用者交易,讓作業可以繼續。Kill all user transactions that block the online index rebuild DDL operation so that the operation can continue. BLOCKERS 選項需要登入才能擁有 ALTER ANY CONNECTION 權限。The BLOCKERS option requires the login to have ALTER ANY CONNECTION permission.

RESUMERESUME

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

繼續以手動方式暫停或因失敗而暫停的索引作業。Resume an index operation that is paused manually or due to a failure.

MAX_DURATION 與 RESUMABLE=ON 搭配使用MAX_DURATION used with RESUMABLE=ON

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

可繼續的線上索引作業在繼續之後的執行時間 (以分鐘為單位指定的整數值)。The time (an integer value specified in minutes) that the resumable online index operation is executed after being resumed. 時間到期之後,如果可繼續的作業仍在執行中,就會暫停作業。Once the time expires, the resumable operation is paused if it is still running.

WAIT_AT_LOW_PRIORITY 與 RESUMABLE=ONONLINE = ON 搭配使用。WAIT_AT_LOW_PRIORITY used with RESUMABLE=ON and ONLINE = ON.

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

在暫停之後繼續線上索引重建時,必須等候這個資料表的封鎖作業。Resuming an online index rebuild after a pause has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY 表示線上索引重建作業將會等候低優先順序鎖定,讓其他作業在線上索引建立作業等候時繼續進行。WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. 省略 WAIT AT LOW PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). 如需詳細資訊,請參閱 WAIT_AT_LOW_PRIORITYFor more information, see WAIT_AT_LOW_PRIORITY.

PAUSEPAUSE

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

暫停可繼續的線上索引重建作業。Pause a resumable online index rebuild operation.

ABORTABORT

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

中止已宣告為可繼續的執行中或已暫停的索引作業。Abort a running or paused index operation that was declared as resumable. 您必須明確地執行 ABORT 命令,以終止可繼續的索引重建作業。You have to explicitly execute an ABORT command to terminate a resumable index rebuild operation. 失敗或暫停可繼續的索引作業不會終止其執行;相反地,它會使作業進入無限期暫停狀態。Failure or pausing a resumable index operation does not terminate its execution; rather, it leaves the operation in an indefinite pause state.

備註Remarks

ALTER INDEX 無法用來重新進行索引的分割區,或將它移到另一個檔案群組。ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. 您不能利用這個陳述式來修改索引定義,例如新增或刪除資料行,或變更資料行順序。This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order. 請搭配 DROP_EXISTING 子句來使用 CREATE INDEX,以執行這些作業。Use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

未明確指定選項時,會套用目前的設定。When an option is not explicitly specified, the current setting is applied. 例如,如果 REBUILD 子句並未指定 FILLFACTOR 設定,在重建過程中,會使用系統目錄中所儲存的填滿因數值。For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. 若要檢視目前的索引選項設定,請使用 sys.indexesTo view the current index option settings, use sys.indexes.

ONLINEMAXDOPSORT_IN_TEMPDB 的值並未儲存在系統目錄中。The values for ONLINE, MAXDOP, and SORT_IN_TEMPDB are not stored in the system catalog. 除非索引陳述式中另有指定,否則會使用選項的預設值。Unless specified in the index statement, the default value for the option is used.

在多重處理器的電腦上,ALTER INDEX REBUILD 也如同其他查詢一樣,會自動使用更多處理器來執行與修改索引相關的掃描和排序作業。On multiprocessor computers, just like other queries do, ALTER INDEX REBUILD automatically uses more processors to perform the scan and sort operations that are associated with modifying the index. 當您執行 ALTER INDEX REORGANIZE 時,不論是否設定了 LOB_COMPACTION,max degree of parallelism 值都是單一執行緒作業。When you run ALTER INDEX REORGANIZE, with or without LOB_COMPACTION, the max degree of parallelism value is a single threaded operation. 如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

重要

如果索引所在的檔案群組離線或設為唯讀,便無法重新組織或重建索引。An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. 當指定了 ALL 關鍵字,且有一個或多個索引在離線或唯讀檔案群組中,陳述式會失敗。When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

重建索引Rebuilding Indexes

重建索引會卸除和重新建立索引。Rebuilding an index drops and re-creates the index. 這會移除片段;根據指定的或現有的填滿因數設定壓縮頁面來收回磁碟空間,以及重新排序連續頁面中的索引資料列。This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. 當指定 ALL 時,會在單一交易中卸除和重建資料表的所有索引。When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. 不需要事先卸除外部索引鍵條件約束。Foreign key constraints do not have to be dropped in advance. 當重建含有 128 個或更多範圍的索引時,Database EngineDatabase Engine 會延遲取消配置實際的頁面,也會延遲其關聯鎖定,直到認可交易之後。When indexes with 128 extents or more are rebuilt, the Database EngineDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

如需詳細資訊,請參閱 重新組織與重建索引For more information, see Reorganize and Rebuild Indexes.

重新組織索引Reorganizing Indexes

重新組織索引所用的系統資源最少。Reorganizing an index uses minimal system resources. 它會實際重新排序分葉層級的頁面,使它們由左至右符合分葉節點的邏輯順序,以重新組織資料表和檢視表之叢集和非叢集索引的分葉層級。It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. 重新組織也會壓縮索引頁面。Reorganizing also compacts the index pages. 壓縮是以現有填滿因數值為基礎。Compaction is based on the existing fill factor value.

當指定 ALL 時,會重新組織資料表的叢集和非叢集關聯式索引及 XML 索引。When ALL is specified, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized. 當指定 ALL 時,適用某些限制,請參閱本文<引數>一節中的 ALL 定義。Some restrictions apply when specifying ALL, refer to the definition for ALL in the Arguments section of this article.

如需詳細資訊,請參閱 重新組織與重建索引For more information, see Reorganize and Rebuild Indexes.

重要

若為具有已排序叢集資料行存放區索引的 Azure SQL 資料倉儲資料表,則 ALTER INDEX REORGANIZE 不會重新排序資料。For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. 若要重新排序資料,請使用 ALTER INDEX REBUILDTo resort the data use ALTER INDEX REBUILD.

停用索引Disabling Indexes

停用索引可防止使用者存取索引,如果是叢集索引,則可防止使用者存取基礎資料表的資料。Disabling an index prevents user access to the index, and for clustered indexes, to the underlying table data. 索引定義會保留在系統目錄中。The index definition remains in the system catalog. 停用檢視的非叢集索引或叢集索引時,會實際刪除索引資料。Disabling a nonclustered index or clustered index on a view physically deletes the index data. 停用叢集索引可防止存取資料,資料仍會保留在 B 型樹狀目錄中,但不進行維護,直到卸除或重建索引為止。Disabling a clustered index prevents access to the data, but the data remains unmaintained in the B-tree until the index is dropped or rebuilt. 若要檢視已啟用或已停用索引的狀態,請查詢 sys.indexes 目錄檢視中的 is_disabled 資料行。To view the status of an enabled or disabled index, query the is_disabled column in the sys.indexes catalog view.

如果資料表在異動複寫發行集中,您便無法停用關聯於主索引鍵資料行的任何索引。If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. 複寫需要這些索引。These indexes are required by replication. 若要停用索引,您必須先從發行集中卸除資料表。To disable an index, you must first drop the table from the publication. 如需詳細資訊,請參閱發行資料和資料庫物件For more information, see Publish Data and Database Objects.

使用 ALTER INDEX REBUILD 陳述式或 CREATE INDEX WITH DROP_EXISTING 陳述式來啟用索引。Use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement to enable the index. 當 ONLINE 選項設為 ON 時,無法重建停用的叢集索引。Rebuilding a disabled clustered index cannot be performed with the ONLINE option set to ON. 如需詳細資訊,請參閱 停用索引和條件約束For more information, see Disable Indexes and Constraints.

設定選項Setting Options

您可以在不重建或重新組織指定之索引的情況下,設定這個索引的 ALLOW_ROW_LOCKSALLOW_PAGE_LOCKSOPTIMIZE_FOR_SEQUENTIAL_KEYIGNORE_DUP_KEYSTATISTICS_NORECOMPUTE 選項。You can set the options ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEY and STATISTICS_NORECOMPUTE for a specified index without rebuilding or reorganizing that index. 修改的值會立即套用在索引上。The modified values are immediately applied to the index. 若要檢視這些設定,請使用 sys.indexesTo view these settings, use sys.indexes. 如需詳細資訊,請參閱 設定索引選項For more information, see Set Index Options.

資料列和頁面鎖定選項Row and Page Locks Options

如果 ALLOW_ROW_LOCKS = ONALLOW_PAGE_LOCK = ON,當您存取索引時,允許資料列、頁面和資料表層級的鎖定。When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-level, page-level, and table-level locks are allowed when you access the index. Database EngineDatabase Engine 會選擇適當的鎖定,且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。The Database EngineDatabase Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock.

如果 ALLOW_ROW_LOCKS = OFFALLOW_PAGE_LOCK = OFF,當您存取索引時,只允許資料表層級的鎖定。When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when you access the index.

如果指定 ALL,且設定了資料列或頁面鎖定,便會將這些設定套用至所有索引上。If ALL is specified when the row or page lock options are set, the settings are applied to all indexes. 當基礎資料表是堆積時,會依照下列方式來套用設定:When the underlying table is a heap, the settings are applied in the following ways:

ALLOW_ROW_LOCKS = ON 或 OFFALLOW_ROW_LOCKS = ON or OFF 套用在堆積和任何相關聯的非叢集索引上。To the heap and any associated nonclustered indexes.
ALLOW_PAGE_LOCKS = ONALLOW_PAGE_LOCKS = ON 套用在堆積和任何相關聯的非叢集索引上。To the heap and any associated nonclustered indexes.
ALLOW_PAGE_LOCKS = OFFALLOW_PAGE_LOCKS = OFF 完整套用在非叢集索引上。Fully to the nonclustered indexes. 這表示在非叢集索引上,不允許所有頁面鎖定。This means that all page locks are not allowed on the nonclustered indexes. 在堆積上,不允許的鎖定只有頁面的共用 (S)、更新 (U) 和獨佔 (X) 鎖定。On the heap, only the shared (S), update (U) and exclusive (X) locks for the page are not allowed. Database EngineDatabase Engine 仍能取得意圖頁面鎖定 (IS、IU 或 IX),供內部使用。The Database EngineDatabase Engine can still acquire an intent page lock (IS, IU or IX) for internal purposes.

線上索引作業Online Index Operations

當重建索引且 ONLINE 選項設為 ON 時,查詢和資料修改可以使用基礎物件、資料表和相關聯的索引。When rebuilding an index and the ONLINE option is set to ON, the underlying objects, the tables and associated indexes, are available for queries and data modification. 您也可以在線上重建位於單一分割區之索引的一部分。You can also rebuild online a portion of an index residing on a single partition. 在改變過程中,只會在非常短的時間內,保留獨佔的資料表鎖定。Exclusive table locks are held only for a very short amount of time during the alteration process.

索引一律是在線上重新組織。Reorganizing an index is always performed online. 這個過程不會長期保留鎖定,因此,不會封鎖執行中的查詢或更新。The process does not hold locks long term and, therefore, does not block queries or updates that are running.

只有在執行下列動作時,您才能在相同資料表或資料表分割區上執行並行的線上索引作業:You can perform concurrent online index operations on the same table or table partition only when doing the following:

  • 建立多個非叢集索引。Creating multiple nonclustered indexes.
  • 在相同資料表上重新組織不同的索引。Reorganizing different indexes on the same table.
  • 在重建相同資料表的非重疊索引時,重新組織不同的索引。Reorganizing different indexes while rebuilding nonoverlapping indexes on the same table.

同時執行的所有其他線上索引作業都會失敗。All other online index operations performed at the same time fail. 例如,您不能在相同資料表上,同時重建兩個或更多索引,或在相同資料表上重建現有索引時,建立新的索引。For example, you cannot rebuild two or more indexes on the same table concurrently, or create a new index while rebuilding an existing index on the same table.

可繼續的索引作業Resumable index operations

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

線上索引重建已使用 RESUMABLE = ON 選項指定為可繼續。Online index rebuild is specified as resumable using the RESUMABLE = ON option.

  • 指定索引的中繼資料中不會保存 RESUMABLE 選項,並且僅適用於目前 DDL 陳述式的持續時間。The RESUMABLE option is not persisted in the metadata for a given index and applies only to the duration of a current DDL statement. 因此,必須明確指定 RESUMABLE = ON 子句,才能啟用可繼續性。Therefore, the RESUMABLE = ON clause must be specified explicitly to enable resumability.

  • RESUMABLE = ON 選項或 low_priority_lock_wait 引數選項支援 MAX_DURATION 選項。MAX_DURATION option is supported for RESUMABLE = ON option or the low_priority_lock_wait argument option.

    • RESUMABLE 選項的 MAX_DURATION 可指定重建索引時的時間間隔。MAX_DURATION for RESUMABLE option specifies the time interval for an index being rebuild. 使用此時間之後,索引重建就會暫停或完成執行。Once this time is used the index rebuild is either paused or it completes its execution. 使用者可決定何時可以繼續已暫停索引的重建。User decides when a rebuild for a paused index can be resumed. MAX_DURATION 的時間是以分鐘計算,且必須大於 0 分鐘,並少於或等於一週 (7 * 24 * 60 = 10080 分鐘)。The time in minutes for MAX_DURATION must be greater than 0 minutes and less or equal one week (7 * 24 * 60 = 10080 minutes). 索引作業長時間暫停可能會影響特定資料表上的 DML 效能,以及影響資料庫磁碟容量,因為原始索引和新建立的索引都需要磁碟空間,且需要在 DML 作業期間更新。Having a long pause for an index operation may impact the DML performance on a specific table as well as the database disk capacity since both indexes the original one and the newly created one require disk space and need to be updated during DML operations. 如果省略 MAX_DURATION 選項,索引作業將會繼續執行直到完成或發生失敗為止。If MAX_DURATION option is omitted, the index operation will continue until its completion or until a failure occurs.
    • <low_priority_lock_wait> 引數選項可讓您決定索引作業在 SCH-M 鎖定上遭到封鎖時可繼續作業的方式。The <low_priority_lock_wait> argument option allows you to decide how the index operation can proceed when blocked on the SCH-M lock.
  • 重新執行具有相同參數的原始 ALTER INDEX REBUILD 陳述式,就會繼續已暫停的索引重建作業。Re-executing the original ALTER INDEX REBUILD statement with the same parameters resumes a paused index rebuild operation. 您也可以執行 ALTER INDEX RESUME 陳述式,繼續已暫停的索引重建作業。You can also resume a paused index rebuild operation by executing the ALTER INDEX RESUME statement.

  • 可繼續的索引不支援 SORT_IN_TEMPDB=ON 選項The SORT_IN_TEMPDB=ON option is not supported for resumable index

  • RESUMABLE=ON 的 DDL 命令無法在明確交易內部執行 (不能是 begin tran ... commit 區塊的一部分)。The DDL command with RESUMABLE=ON cannot be executed inside an explicit transaction (cannot be part of begin tran ... commit block).

  • 只有已暫停的索引作業才能繼續。Only index operations that are paused are resumable.

  • 繼續已暫停的索引作業時,您可以將 MAXDOP 值變更為新值。When resuming an index operation that is paused, you can change the MAXDOP value to a new value. 如果繼續已暫停的索引作業時未指定 MAXDOP,會使用最後一個 MAXDOP 值。If MAXDOP is not specified when resuming an index operation that is paused, the last MAXDOP value is taken. 如果沒有為索引重建作業指定 MAXDOP 選項,會使用預設值。IF the MAXDOP option is not specified at all for index rebuild operation, the default value is taken.

  • 若要立即暫停索引作業,您可以停止進行中的命令 (Ctrl-C),或執行 ALTER INDEX PAUSE 命令或 KILL session_id 命令。To pause immediately the index operation, you can stop the ongoing command (Ctrl-C) or you can execute the ALTER INDEX PAUSE command or the KILL session_id command. 暫停命令之後,可以使用 RESUME 選項繼續執行命令。Once the command is paused it can be resumed using RESUME option.

  • ABORT 命令會終止裝載原始索引重建的工作階段,並中止索引作業The ABORT command kills the session that hosted the original index rebuild and aborts the index operation

  • 可繼續的索引重建不需要額外的資源,除了No extra resources are required for resumable index rebuild except for

    • 需要額外的空間以保留正在建立的索引,包括索引的暫停時間Additional space required to keep the index being built, including the time when index is being paused
    • 可防止進行任何 DDL 修改的 DDL 狀態A DDL state preventing any DDL modification
  • 準刪除清除將會在索引暫停階段期間執行,但它將會在索引執行期間暫停The ghost cleanup will be running during the index pause phase, but it will be paused during index run
    已針對可繼續的索引重建作業停用下列功能The following functionality is disabled for resumable index rebuild operations

    • RESUMABLE=ON 不支援重建已停用的索引Rebuilding an index that is disabled is not supported with RESUMABLE=ON
    • ALTER INDEX REBUILD ALL 命令ALTER INDEX REBUILD ALL command
    • ALTER TABLE 使用索引重建ALTER TABLE using index rebuild
    • "RESUMABLE = ON" 的 DDL 命令無法在明確交易內部執行 (不能是 begin tran ... commit 區塊的一部分)DDL command with "RESUMEABLE = ON" cannot be executed inside an explicit transaction (cannot be part of begin tran ... commit block)
    • 重建已經計算或以 TIMESTAMP 資料行作為索引鍵資料行的索引。Rebuild an index that has computed or TIMESTAMP column(s) as key columns.
  • 如果基底資料表包含 LOB 資料行,可繼續的叢集索引重建就會在此作業開始時需要 Sch-M 鎖定In case the base table contains LOB column(s) resumable clustered index rebuild requires a Sch-M lock in the Starting of this operation

注意

DDL 命令會執行,直到完成、暫停或失敗為止。The DDL command runs until it completes, pauses or fails. 如果命令暫停,將會發出錯誤指出作業已暫停,而且沒有完成索引建立。In case the command pauses, an error will be issued indicating that the operation was paused and that the index creation did not complete. 您可以從 sys.index_resumable_operations 取得目前索引狀態的詳細資訊。More information about the current index status can be obtained from sys.index_resumable_operations. 和以前一樣,如果發生失敗,也會發出錯誤。As before in case of a failure an error will be issued as well.

如需詳細資訊,請參閱 Perform Index Operations OnlineFor more information, see Perform Index Operations Online.

WAIT_AT_LOW_PRIORITY 與線上索引作業WAIT_AT_LOW_PRIORITY with online index operations

為了執行線上索引重建的 DDL 陳述式,特定資料表上執行的所有使用中封鎖交易都必須完成。In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. 當線上索引重建執行時,它會封鎖這個資料表上準備開始執行的所有新交易。When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. 雖然線上索引重建的鎖定期間很短,但是等候特定資料表上所有未完成的交易完成並封鎖要開始的新交易,可能會大幅影響輸送量,導致工作負載速度變慢或逾時,並且大幅限制對基礎資料表的存取。Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. WAIT_AT_LOW_PRIORITY 選項可讓 DBA 管理線上索引重建所需的 S 鎖定和 Sch-M 鎖定,並允許它們選取 3 個選項的其中一個。The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. 在這 3 個案例中,如果在等候期間 ( (MAX_DURATION = n [minutes]) ) 沒有封鎖活動,線上索引重建會立即執行而不等候,並且 DDL 陳述式會完成。In all 3 cases, if during the wait time ( (MAX_DURATION = n [minutes]) ), there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.

空間索引的限制Spatial Index Restrictions

當您重建空間索引時,在索引作業的持續時間,無法使用基礎使用者資料表,因為空間索引會持有結構描述鎖定。When you rebuild a spatial index, the underlying user table is unavailable for the duration of the index operation because the spatial index holds a schema lock.

使用者資料表中的 PRIMARY KEY 條件約束無法在空間索引定義於該資料表的資料行上時,加以修改。The PRIMARY KEY constraint in the user table cannot be modified while a spatial index is defined on a column of that table. 若要變更 PRIMARY KEY 條件約束,請先卸除此資料表的每一個空間索引。To change the PRIMARY KEY constraint, first drop every spatial index of the table. 在修改 PRIMARY KEy 條件約束之後,您可以重新建立每一個空間索引。After modifying the PRIMARY KEy constraint, you can re-create each of the spatial indexes.

在單一分割區重建作業中,您不能指定任何空間索引。In a single partition rebuild operation, you cannot specify any spatial indexes. 但是,您可以在完整分割區重建中指定空間索引。However, you can specify spatial indexes in a complete partition rebuild.

若要變更空間索引特定的選項 (例如 BOUNDING_BOX 或 GRID),您可以使用指定 DROP_EXISTING = ON 的 CREATE SPATIAL INDEX 陳述式,或是卸除此空間索引並建立新的索引。To change options that are specific to a spatial index, such as BOUNDING_BOX or GRID, you can either use a CREATE SPATIAL INDEX statement that specifies DROP_EXISTING = ON, or drop the spatial index and create a new one. 如需範例,請參閱 CREATE SPATIAL INDEX (Transact-SQL)中的<備註>一節。For an example, see CREATE SPATIAL INDEX (Transact-SQL).

資料壓縮Data Compression

如需資料壓縮的詳細資訊,請參閱 資料壓縮For a more information about data compression, see Data Compression.

若要評估變更 PAGE 和 ROW 壓縮如何影響資料表、索引或分割區,請使用 sp_estimate_data_compression_savings 預存程序。To evaluate how changing PAGE and ROW compression will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

下列限制適用於分割區索引:The following restrictions apply to partitioned indexes:

  • 使用 ALTER INDEX ALL ... 時,您無法在資料表具有非對齊索引時變更單一分割區的壓縮設定。When you use ALTER INDEX ALL ..., you cannot change the compression setting of a single partition if the table has nonaligned indexes.
  • ALTER INDEX <index> ...REBUILD PARTITION ... 語法會重建此索引的指定分割區。The ALTER INDEX <index> ... REBUILD PARTITION ... syntax rebuilds the specified partition of the index.
  • ALTER INDEX <index> ...REBUILD WITH ... 語法會重建此索引的所有分割區。The ALTER INDEX <index> ... REBUILD WITH ... syntax rebuilds all partitions of the index.

統計資料Statistics

當您針對資料表執行 ALTER INDEX ALL ... 時,只會更新與索引相關聯的統計資料。When you execute ALTER INDEX ALL ... on a table, only the statistics associates with indexes are updated. 針對資料表 (而非索引) 所建立的自動或手動統計資料不會進行更新。Automatic or manual statistics created on the table (instead of an index) are not updated.

權限Permissions

若要執行 ALTER INDEX,至少需要資料表或檢視表的 ALTER 權限。To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

版本資訊Version Notes

  • SQL DatabaseSQL Database 不使用 filegroup 和 filestream 選項。does not use filegroup and filestream options.
  • SQL Server 2012 (11.x)SQL Server 2012 (11.x) 之前無法使用資料行存放區索引。Columnstore indexes are not available prior to SQL Server 2012 (11.x)SQL Server 2012 (11.x).
  • 可繼續的索引作業從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) Azure SQL DatabaseAzure SQL Database 起開始提供Resumable index operations are available Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) Azure SQL DatabaseAzure SQL Database

基本語法範例:Basic syntax example:

ALTER INDEX index1 ON table1 REBUILD;  
  
ALTER INDEX ALL ON table1 REBUILD;  
  
ALTER INDEX ALL ON dbo.table1 REBUILD;  

範例:資料行存放區索引Examples: Columnstore Indexes

這些範例適用於資料行存放區索引。These examples apply to columnstore indexes.

A.A. REORGANIZE 示範REORGANIZE demo

此範例示範 ALTER INDEX REORGANIZE 命令的運作方式。This example demonstrates how the ALTER INDEX REORGANIZE command works. 它會建立一個擁有多個資料列群組的資料表,然後示範 REORGANIZE 如何合併資料列群組。It creates a table that has multiple rowgroups, and then demonstrates how REORGANIZE merges the rowgroups.

-- Create a database   
CREATE DATABASE [ columnstore ];  
GO  
  
-- Create a rowstore staging table  
CREATE TABLE [ staging ] (  
     AccountKey              int NOT NULL,  
     AccountDescription      nvarchar (50),  
     AccountType             nvarchar(50),  
     AccountCodeAlternateKey     int  
     )  
  
-- Insert 10 million rows into the staging table.   
DECLARE @loop int  
DECLARE @AccountDescription varchar(50)  
DECLARE @AccountKey int  
DECLARE @AccountType varchar(50)  
DECLARE @AccountCode int  
  
SELECT @loop = 0  
BEGIN TRAN  
    WHILE (@loop < 300000)   
      BEGIN  
        SELECT @AccountKey = CAST (RAND()*10000000 as int);  
        SELECT @AccountDescription = 'accountdesc ' + CONVERT(varchar(20), @AccountKey);  
        SELECT @AccountType = 'AccountType ' + CONVERT(varchar(20), @AccountKey);  
        SELECT @AccountCode =  CAST (RAND()*10000000 as int);  
  
        INSERT INTO  staging VALUES (@AccountKey, @AccountDescription, @AccountType, @AccountCode);  
  
        SELECT @loop = @loop + 1;  
    END  
COMMIT  
  
-- Create a table for the clustered columnstore index  
  
CREATE TABLE cci_target (  
     AccountKey              int NOT NULL,  
     AccountDescription      nvarchar (50),  
     AccountType             nvarchar(50),  
     AccountCodeAlternateKey int  
     )  
  
-- Convert the table to a clustered columnstore index named inxcci_cci_target;  
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;  

使用 TABLOCK 選項來以平行處理方式插入資料列。Use the TABLOCK option to insert rows in parallel. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,INSERT INTO 作業可以在使用 TABLOCK 時以平行處理方式執行。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the INSERT INTO operation can run in parallel when TABLOCK is used.

INSERT INTO cci_target WITH (TABLOCK) 
SELECT TOP 300000 * FROM staging;  

執行此命令可查看開啟的差異資料列群組。Run this command to see the OPEN delta rowgroups. 資料列群組的數目取決於平行處理原則的程度。The number of rowgroups depends on the degree of parallelism.

SELECT *   
FROM sys.dm_db_column_store_row_group_physical_stats   
WHERE object_id  = object_id('cci_target');  

執行此命令以將所有關閉和開啟的資料列群組強制移動到資料行存放區中。Run this command to force all CLOSED and OPEN rowgroups into the columnstore.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);  

再次執行此命令,您將會看到幾個較小的資料列群組合併成一個壓縮的資料列群組。Run this command again and you will see that smaller rowgroups are merged into one compressed rowgroup.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);  

B.B. 將關閉的差異資料列群組壓縮到資料行存放區中Compress CLOSED delta rowgroups into the columnstore

此範例使用 REORGANIZE 選項,以壓縮的資料列群組方式,將每個關閉的差異資料列群組壓縮到資料行存放區中。This example uses the REORGANIZE option to compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. 這是非必要的,但當 tuple-mover 壓縮關閉的資料列群組的速度不夠快時,就很有用。This is not necessary, but is useful when the tuple-mover is not compressing CLOSED rowgroups fast enough.

-- Uses AdventureWorksDW  
-- REORGANIZE all partitions  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;  
  
-- REORGANIZE a specific partition  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;  

C.C. 將所有開啟和關閉的差異資料列群組壓縮到資料行存放區中Compress all OPEN AND CLOSED delta rowgroups into the columnstore

適用範圍: SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) 命令會以壓縮的資料列群組方式,將每個 OPEN 和 CLOSED 的差異資料列群組壓縮到資料行存放區中。The command REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) compresses each OPEN and CLOSED delta rowgroup into the columnstore as a compressed rowgroup. 這會清空差異存放區,並將所有資料列強制壓縮到資料行存放區。This empties the deltastore and forces all rows to get compressed into the columnstore. 這在執行許多插入作業之後特別有用,因為這些作業會將資料列儲存在一或多個差異資料行群組中。This is useful especially after performing many insert operations since these operations store the rows in one or more delta rowgroups.

REORGANIZE 可合併資料列群組,讓資料列群組中的資料列數目最高達到資料列 <= 1,024,576 的數目上限。REORGANIZE combines rowgroups to fill rowgroups up to a maximum number of rows <= 1,024,576. 因此當您壓縮所有開啟和關閉的資料列群組時,不會產生裡面只有幾個資料列的大量已壓縮資料列群組。Therefore, when you compress all OPEN and CLOSED rowgroups you won't end up with lots of compressed rowgroups that only have a few rows in them. 您可以將資料列群組盡量填滿,以縮小壓縮的大小並增進查詢效能。You want rowgroups to be as full as possible to reduce the compressed size and improve query performance.

-- Uses AdventureWorksDW2016  
-- Move all OPEN and CLOSED delta rowgroups into the columnstore.  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);  
  
-- For a specific partition, move all OPEN AND CLOSED delta rowgroups into the columnstore  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);  

D.D. 線上重組資料行存放區索引Defragment a columnstore index online

不適用於:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)Does not apply to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x).

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,REORGANIZE 不只能將差異資料列群組壓縮到資料行存放區,還能執行其他作業。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), REORGANIZE does more than compress delta rowgroups into the columnstore. 它也會執行線上重組。It also performs online defragmentation. 首先,它會在資料列群組中 10% 或更多資料列已遭到刪除時,實際移除已刪除的資料列,以縮小資料行存放區大小。First, it reduces the size of the columnstore by physically removing deleted rows when 10% or more of the rows in a rowgroup have been deleted. 然後,它會合併資料列群組以構成較大的資料列群組,每個資料列群組最多可包含 1,024,576 個資料列。Then, it combines rowgroups together to form larger rowgroups that have up to the maximum of 1,024,576 rows per rowgroups. 所有變更的資料列群組都會重新壓縮。All rowgroups that are changed get re-compressed.

注意

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,在大部分情況下已不再需要重建資料行存放區索引,因為 REORGANIZE 會實際移除已刪除的資料列並合併資料列群組。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), rebuilding a columnstore index is no longer necessary in most situations since REORGANIZE physically removes deleted rows and merges rowgroups. COMPRESS_ALL_ROW_GROUPS 選項會將所有開啟或關閉的差異資料列群組強制移動到資料行存放區中,之前只能使用重建執行這項作業。The COMPRESS_ALL_ROW_GROUPS option forces all OPEN or CLOSED delta rowgroups into the columnstore which previously could only be done with a rebuild. REORGANIZE 在線上且會在背景執行,因此可以在作業執行時繼續進行查詢。REORGANIZE is online and occurs in the background so queries can continue as the operation happens.

-- Uses AdventureWorks  
-- Defragment by physically removing rows that have been logically deleted from the table, and merging rowgroups.  
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;  

E.E. 離線重建叢集資料行存放區索引Rebuild a clustered columnstore index offline

適用於:SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x))

提示

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起且在 Azure SQL DatabaseAzure SQL Database 中,我們建議使用 ALTER INDEX REORGANIZE,不要使用 ALTER INDEX REBUILD。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database, we recommend using ALTER INDEX REORGANIZE instead of ALTER INDEX REBUILD.

注意

SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,REORGANIZE 僅用於將關閉的資料列群組壓縮到資料行存放區。In SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x), REORGANIZE is only used to compress CLOSED rowgroups into the columnstore. 若要執行重組作業並將所有差異資料列群組強制移動到資料行存放區,重建索引是唯一的方式。The only way to perform defragmentation operations and to force all delta rowgroups into the columnstore is to rebuild the index.

此範例說明如何重建叢集資料行存放區索引,並將所有差異資料列群組強制移動到資料行存放區。This example shows how to rebuild a clustered columnstore index and force all delta rowgroups into the columnstore. 第一個步驟是準備包含叢集資料行存放區索引的 FactInternetSales2 資料表,並插入前四個資料行的資料。This first step prepares a table FactInternetSales2 with a clustered columnstore index and inserts data from the first four columns.

-- Uses AdventureWorksDW  
  
CREATE TABLE dbo.FactInternetSales2 (  
    ProductKey [int] NOT NULL,   
    OrderDateKey [int] NOT NULL,   
    DueDateKey [int] NOT NULL,   
    ShipDateKey [int] NOT NULL);  
  
CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2  
ON dbo.FactInternetSales2;  
  
INSERT INTO dbo.FactInternetSales2  
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey  
FROM dbo.FactInternetSales;  
  
SELECT * FROM sys.column_store_row_groups;  

結果顯示有一個開啟的資料列群組,這表示 SQL ServerSQL Server 將會等候更多的資料列加入,然後才會關閉資料列群組,並將資料移到資料行存放區。The results show there is one OPEN rowgroup, which means SQL ServerSQL Server will wait for more rows to be added before it closes the rowgroup and moves the data to the columnstore. 下一個陳述式會重建叢集資料行存放區索引,這會將所有資料列強制移動到資料行存放區中。This next statement rebuilds the clustered columnstore index, which forces all rows into the columnstore.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;  
SELECT * FROM sys.column_store_row_groups;  

SELECT 陳述式的結果顯示資料列群組為 COMPRESSED,這表示資料列群組的資料行區段現在已壓縮,而且儲存在資料行存放區中。The results of the SELECT statement show the rowgroup is COMPRESSED, which means the column segments of the rowgroup are now compressed and stored in the columnstore.

F.F. 離線重建叢集資料行存放區索引的分割區Rebuild a partition of a clustered columnstore index offline

適用於SQL ServerSQL Server (從 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x))

若要重建大型叢集資料行存放區索引的分割區,請使用 ALTER INDEX REBUILD 與分割區選項。To rebuild a partition of a large clustered columnstore index, use ALTER INDEX REBUILD with the partition option. 這個範例會重建分割區 12。This example rebuilds partition 12. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,我們建議使用 REORGANIZE 取代 REBUILD。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), we recommend replacing REBUILD with REORGANIZE.

ALTER INDEX cci_fact3   
ON fact3  
REBUILD PARTITION = 12;  

G.G. 變更叢集資料行存放區索引以使用封存壓縮Change a clustered columstore index to use archival compression

不適用於:SQL Server 2012 (11.x)SQL Server 2012 (11.x)Does not apply to: SQL Server 2012 (11.x)SQL Server 2012 (11.x)

您可以使用 COLUMNSTORE_ARCHIVE 資料壓縮選項,進一步縮小叢集資料行存放區索引的大小。You can choose to reduce the size of a clustered columnstore index even further by using the COLUMNSTORE_ARCHIVE data compression option. 這項功能非常適合用於想要保存在較便宜儲存裝置上的較舊資料。This is practical for older data that you want to keep on cheaper storage. 我們建議只針對不常存取的資料使用此功能,因為解壓縮速度會比使用一般的 COLUMNSTORE 壓縮慢。We recommend only using this on data that is not accessed often since decompress is slower than with the normal COLUMNSTORE compression.

下列範例會重建叢集資料行存放區索引來使用封存壓縮,然後示範如何移除封存壓縮。The following example rebuilds a clustered columnstore index to use archival compression, and then shows how to remove the archival compression. 最後的結果只會使用資料行存放區壓縮。The final result will use only columnstore compression.

--Prepare the example by creating a table with a clustered columnstore index.  
CREATE TABLE SimpleTable (  
    ProductKey [int] NOT NULL,   
    OrderDateKey [int] NOT NULL,   
    DueDateKey [int] NOT NULL,   
    ShipDateKey [int] NOT NULL  
);  
  
CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);  
  
CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable  
ON SimpleTable  
WITH (DROP_EXISTING = ON);  
  
--Compress the table further by using archival compression.  
ALTER INDEX cci_SimpleTable ON SimpleTable  
REBUILD  
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);  
  
--Remove the archive compression and only use columnstore compression.  
ALTER INDEX cci_SimpleTable ON SimpleTable  
REBUILD  
WITH (DATA_COMPRESSION = COLUMNSTORE);  
GO  

範例:資料列存放區索引Examples: Rowstore indexes

A.A. 重建索引Rebuilding an index

下列範例會在 AdventureWorks2012AdventureWorks2012 資料庫的 Employee 資料表上重建單一索引。The following example rebuilds a single index on the Employee table in the AdventureWorks2012AdventureWorks2012 database.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;  

B.B. 在資料表上重建所有索引以及指定選項Rebuilding all indexes on a table and specifying options

下列範例指定 ALL 關鍵字。The following example specifies the keyword ALL. 這樣會重建與 AdventureWorks2012AdventureWorks2012 資料庫中 Production.Product 資料表相關聯的所有索引。This rebuilds all indexes associated with the table Production.Product in the AdventureWorks2012AdventureWorks2012 database. 指定三個選項。Three options are specified.

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

ALTER INDEX ALL ON Production.Product  
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);  

下列範例會加入包括低優先權鎖定選項的 ONLINE 選項,並加入資料列壓縮選項。The following example adds the ONLINE option including the low priority lock option, and adds the row compression option.

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

ALTER INDEX ALL ON Production.Product  
REBUILD WITH   
(  
    FILLFACTOR = 80,   
    SORT_IN_TEMPDB = ON,  
    STATISTICS_NORECOMPUTE = ON,  
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),   
    DATA_COMPRESSION = ROW  
);  

C.C. 重新組織具有 LOB 壓縮的索引Reorganizing an index with LOB compaction

下列範例會重新組織 AdventureWorks2012AdventureWorks2012 資料庫中的單一叢集索引。The following example reorganizes a single clustered index in the AdventureWorks2012AdventureWorks2012 database. 由於索引在分葉層級中包含 LOB 資料類型,因此,這個陳述式也會壓縮包含大型物件資料的所有頁面。Because the index contains a LOB data type in the leaf level, the statement also compacts all pages that contain the large object data. 請注意,您不需要指定 WITH (LOB_COMPACTION = ON) 選項,因為預設值是 ON。Note that specifying the WITH (LOB_COMPACTION = ON) option is not required because the default value is ON.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);  

D.D. 設定索引選項Setting options on an index

下列範例會設定 AdventureWorks2012AdventureWorks2012 資料庫中 AK_SalesOrderHeader_SalesOrderNumber 索引的幾個選項。The following example sets several options on the index AK_SalesOrderHeader_SalesOrderNumber in the AdventureWorks2012AdventureWorks2012 database.

適用於SQL ServerSQL Server (從 SQL Server 2008SQL Server 2008 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2008SQL Server 2008) and Azure SQL DatabaseAzure SQL Database

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON  
    Sales.SalesOrderHeader  
SET (  
    STATISTICS_NORECOMPUTE = ON,  
    IGNORE_DUP_KEY = ON,  
    ALLOW_PAGE_LOCKS = ON  
    ) ;  
GO

E.E. 停用索引Disabling an index

下列範例會停用 AdventureWorks2012AdventureWorks2012 資料庫中 Employee 資料表的非叢集索引。The following example disables a nonclustered index on the Employee table in the AdventureWorks2012AdventureWorks2012 database.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F.F. 停用條件約束Disabling constraints

下列範例會藉由停用 AdventureWorks2012AdventureWorks2012 資料庫中 PRIMARY KEY 索引來停用 PRIMARY KEY 條件約束。The following example disables a PRIMARY KEY constraint by disabling the PRIMARY KEY index in the AdventureWorks2012AdventureWorks2012 database. 基礎資料表的 FOREIGN KEY 條件約束會自動停用,並且會顯示一則警告訊息。The FOREIGN KEY constraint on the underlying table is automatically disabled and warning message is displayed.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;  

結果集會傳回這則警告訊息。The result set returns this warning message.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'  
on table 'EmployeeDepartmentHistory' referencing table 'Department'  
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G.G. 啟用條件約束Enabling constraints

下列範例會啟用 F 範例所停用的 PRIMARY KEY 和 FOREIGN KEY 條件約束。The following example enables the PRIMARY KEY and FOREIGN KEY constraints that were disabled in Example F.

PRIMARY KEY 條件約束是藉由重建 PRIMARY KEY 索引來啟用。The PRIMARY KEY constraint is enabled by rebuilding the PRIMARY KEY index.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;  

然後會啟用 FOREIGN KEY 條件約束。The FOREIGN KEY constraint is then enabled.

ALTER TABLE HumanResources.EmployeeDepartmentHistory  
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;  
GO  

H.H. 重建分割區索引Rebuilding a partitioned index

下列範例會重建 AdventureWorks2012AdventureWorks2012 資料庫中分割區索引 5 的單一分割區,分割區編號是 IX_TransactionHistory_TransactionDateThe following example rebuilds a single partition, partition number 5, of the partitioned index IX_TransactionHistory_TransactionDate in the AdventureWorks2012AdventureWorks2012 database. 分割區 5 在線上重建,而且低優先權鎖定的 10 分鐘等候時間會分別套用至索引重建作業取得的每一個鎖定。Partition 5 is rebuilt online and the 10 minutes wait time for the low priority lock applies separately to every lock acquired by index rebuild operation. 如果在此時間無法取得鎖定來完成索引重建,重建作業陳述式會中止。If during this time the lock cannot be obtained to complete index rebuild, the rebuild operation statement is aborted.

適用於SQL ServerSQL Server (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x)) and Azure SQL DatabaseAzure SQL Database

-- Verify the partitioned indexes.  
SELECT *  
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);  
GO  
--Rebuild only partition 5.  
ALTER INDEX IX_TransactionHistory_TransactionDate  
ON Production.TransactionHistory  
REBUILD Partition = 5   
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));  
GO  

I.I. 變更索引的壓縮設定Changing the compression setting of an index

下列範例會在非分割資料列存放區資料表上重建索引。The following example rebuilds an index on a nonpartitioned rowstore table.

ALTER INDEX IX_INDEX1   
ON T1  
REBUILD   
WITH (DATA_COMPRESSION = PAGE);  
GO  

如需其他資料壓縮範例,請參閱資料壓縮For additional data compression examples, see Data Compression.

J.J. 線上可繼續的索引重建Online resumable index rebuild

適用於SQL ServerSQL Server (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x)) and Azure SQL DatabaseAzure SQL Database

以下範例說明如何使用線上可繼續的索引重建。The following examples show how to use online resumable index rebuild.

  1. 以 MAXDOP=1 的可繼續作業方式,執行線上索引重建。Execute an online index rebuild as resumable operation with MAXDOP=1.

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;
    
  2. 在索引作業暫停之後再次執行相同命令 (請參閱上述說明),會自動繼續索引重建作業。Executing the same command again (see above) after an index operation was paused, resumes automatically the index rebuild operation.

  3. 以 MAX_DURATION 設為 240 分鐘 的可繼續作業方式,執行線上索引重建。Execute an online index rebuild as resumable operation with MAX_DURATION set to 240 minutes.

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=240) ; 
    
  4. 暫停執行中的可繼續的線上索引重建。Pause a running resumable online index rebuild.

    ALTER INDEX test_idx on test_table PAUSE ;
    
  5. 繼續索引重建 (以指定新值將 MAXDOP 設為 4 之可繼續的作業方式執行) 的線上索引重建。Resume an online index rebuild for an index rebuild that was executed as resumable operation specifying a new value for MAXDOP set to 4.

    ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP=4) ;
    
  6. 繼續索引線上重建 (以可繼續的方式執行) 的線上索引重建作業。Resume an online index rebuild operation for an index online rebuild that was executed as resumable. 將 MAXDOP 設為 2,將索引 (以可繼續方式執行) 的執行時間設定為 240 分鐘,並且如果索引在鎖定上遭到封鎖,請等待 10 分鐘,然後在那之後終止所有封鎖者。Set MAXDOP to 2, set the execution time for the index being running as resumable to 240 minutes and in case of an index being blocked on the lock wait 10 minutes and after that kill all blockers.

       ALTER INDEX test_idx on test_table  
          RESUME WITH (MAXDOP=2, MAX_DURATION= 240 MINUTES, 
          WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS)) ;
    
  7. 中止執行中或已暫停的可繼續的索引重建作業。Abort resumable index rebuild operation which is running or paused.

    ALTER INDEX test_idx on test_table ABORT ;
    

另請參閱See Also

SQL Server 索引架構和設計指南 SQL Server Index Architecture and Design Guide
線上執行索引作業 Perform Index Operations Online
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
停用索引和條件約束 Disable Indexes and Constraints
XML 索引 (SQL Server) XML Indexes (SQL Server)
重新組織與重建索引 Reorganize and Rebuild Indexes
sys.dm_db_index_physical_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)