ALTER INDEX (Transact-SQL)

發行項
05/23/2024

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

藉由停用、重建或重新組織索引或設定索引選項，修改現有的資料表或檢視表索引 (資料列存放區、資料行存放區或 XML)。

Syntax

SQL Server、Azure SQL 資料庫和 Azure SQL 受控執行個體的語法。

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_option> [ , ...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
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ 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 }
    | XML_COMPRESSION = { ON | OFF }
    | 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 } )
}

Azure Synapse Analytics 和分析平台系統（PDW）的語法。

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 ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

引數

index_name

索引的名稱。在資料表或檢視內，索引名稱必須是唯一，但在資料庫內就不一定要唯一。索引名稱必須遵照識別碼的規則。

ALL

指定與資料表或檢視表相關聯的所有索引 (不論索引類型為何)。 ALL如果一或多個索引位於離線或唯讀檔案群組中，或一或多個索引類型上不允許指定的作業，則指定會導致語句失敗。下表列出索引作業和不允許的索引類型。

搭配此作業使用關鍵字 ALL	如果資料表有一個或多個下列項目，便告失敗
`REBUILD WITH ONLINE = ON`	XML 索引空間索引數據行存放區索引 ¹
`REBUILD PARTITION = <partition_number>`	未分割索引、XML 索引、空間索引或停用的索引
`REORGANIZE`	設定為的 `ALLOW_PAGE_LOCKS` 索引 `OFF`
`REORGANIZE PARTITION = <partition_number>`	未分割索引、XML 索引、空間索引或停用的索引
`IGNORE_DUP_KEY = ON`	XML 索引空間索引數據行存放區索引 ¹
`ONLINE = ON`	XML 索引空間索引數據行存放區索引 ¹
`RESUMABLE = ON` ²	關鍵詞不支援 `ALL` 可繼續索引

¹ 適用於 SQL Server 2012 （11.x）和更新版本，以及 Azure SQL 資料庫。

² 適用於 SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

如果使用 ALL 指定 PARTITION = <partition_number>，則所有索引都必須對齊。這表示其會根據對等的分割區函數來進行分割。使用 ALL with PARTITION 會導致所有具有相同 <partition_number> 的索引分割區重建或重新組織。如需數據分割索引的詳細資訊，請參閱分割數據表和索引。

如需可在線執行之索引作業的詳細資訊，請參閱在線索引作業的指導方針。

database_name

資料庫的名稱。

schema_name

資料表或檢視所屬之結構描述的名稱。

table_or_view_name

這是與索引相關聯的資料表或檢視表的名稱。若要顯示物件的索引報表，請使用 sys.indexes 目錄檢視。

當database_name是目前資料庫或database_nametempdb，且table_or_view_name開頭#為時，SQL 資料庫支援三部分名稱格式<database_name>.[schema_name].<table_or_view_name>。

REBUILD [ WITH （ <rebuild_index_option> [ ,... n ] ] ]

適用於：SQL Server 2012 （11.x）和更新版本，以及 Azure SQL 資料庫

指定使用相同的數據行、索引類型、唯一性屬性和排序順序重建索引。這個子句相當於 DBCC DBREINDEX。 REBUILD 會啟用停用的索引。除非指定關鍵詞 ALL ，否則重建叢集索引不會重建相關聯的非叢集索引。如果未指定索引選項，便會套用儲存在 sys.indexes 中的現有索引選項值。任何值未儲存在 sys.indexes 中的索引選項，都會套用選項引數定義中所指示的預設值。

如果 ALL 指定，而且基礎表是堆積，則 REBUILD 作業對數據表沒有任何影響。與資料表相關聯的任何非叢集索引都會重建。

如果資料庫復原模式設為大量記錄模式或簡單模式，REBUILD 作業便可以只進行最基本的記錄。

注意

當您重建主要 XML 索引時，在索引作業的持續時間，無法使用基礎使用者資料表。

對於資料行存放區索引，REBUILD 作業：

不使用排序次序。
在進行 REBUILD 時，取得資料表或分割區上的獨佔鎖定。數據在期間 REBUILD是「離線」且無法使用，即使使用 NOLOCK、讀取認可快照集隔離（RCSI）或快照集隔離（SI）。
將所有資料重新壓縮到資料行存放區。當 REBUILD 進行時，有兩個資料行存放區索引複本存在。當 REBUILD 完成時，SQL Server 會刪除原始資料行存放區索引。

如需詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

PARTITION

指定只重建或重新組織索引的一個分割區。 PARTITION 如果 index_name 不是資料分割索引，則無法指定。

PARTITION = ALL 重建所有分割區。

警告

在具有超過 1,000 個數據分割的數據表上建立和重建非對齊索引是可行的，但不受支援。這樣做可能會導致效能降低，或在這些作業期間耗用過多的記憶體。 Microsoft 建議當分割區數超過 1,000 個時，才使用以資料表為準的索引。

partition_number

要重建或重新組織的分割區索引分割區數。 partition_number 是一個可以參考變數的常數運算式。其中包括使用者定義類型變數或函數及使用者定義函數，但無法參考 Transact-SQL 陳述式。 partition_number必須存在，否則陳述式將會失敗。
WITH （ <single_partition_rebuild_index_option> ）

SORT_IN_TEMPDB、MAXDOP、DATA_COMPRESSION 和 XML_COMPRESSION 是 REBUILD 單一分割區 ((PARTITION = partition_number)) 時所能指定的選項。不能在單一分割區 REBUILD 作業中指定 XML 索引。

DISABLE

將索引標示為已停用，無法供資料庫引擎使用。任何索引都可以停用。已停用之索引的索引定義會保留在系統目錄中，但不含基礎索引資料。停用叢集索引可以防止使用者存取基礎資料表資料。若要啟用索引，請使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH DROP_EXISTING。如需詳細資訊，請參閱停用索引和條件約束和啟用索引和條件約束。

REORGANIZE 資料列存放區索引

針對數據列存放區索引， REORGANIZE 指定重新組織索引分葉層級。此 REORGANIZE 工作為：

一律在線上執行。這表示不會保留長期封鎖的資料表鎖定，而且在 ALTER INDEX REORGANIZE 交易期間，可以繼續查詢或更新基礎資料表。
不允許使用停用的索引。
當設定為 OFF時ALLOW_PAGE_LOCKS，不允許。
在交易內執行且交易回復時，不會回復。

注意

當 ALTER INDEX REORGANIZE 使用明確交易 (例如 BEGIN TRAN ... COMMIT/ROLLBACK 內的 ALTER INDEX) 而非預設的隱含交易模式時，REORGANIZE 的鎖定行為會變得更加嚴格，可能因此導致封鎖。如需隱含交易的詳細資訊，請參閱 SET IMPLICIT_TRANSACTIONS。

如需詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

適用於資料列存放區索引。

LOB_COMPACTION = ON

指定壓縮包含下列大型物件 (LOB) 資料類型資料的所有頁面：image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml。壓縮此資料可縮小磁碟上的資料大小。
對於叢集索引，這會壓縮資料表中包含的所有 LOB 資料行。
對於非叢集索引，這會壓縮索引中本身是非索引鍵資料行 (內含資料行) 的所有 LOB 資料行。
REORGANIZE ALL 會在所有索引上執行 LOB_COMPACTION。對於每個索引，這會壓縮叢集索引中的所有 LOB 資料行、基礎資料表，或非叢集索引中包含的資料行。

LOB_COMPACTION = OFF

不壓縮包含大型物件資料的頁面。
OFF 對堆積沒有任何影響。

REORGANIZE 資料行存放區索引

針對數據行存放區索引， REORGANIZE 將每個 CLOSED 差異數據列群組壓縮到數據行存放區中，做為壓縮的數據列群組。 REORGANIZE 作業一律會在線上執行。這表示不會保留長期封鎖的資料表鎖定，而且在 ALTER INDEX REORGANIZE 交易期間，可以繼續查詢或更新基礎資料表。如需詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

REORGANIZE 不需要將差異數據列群組移至 CLOSED 壓縮的數據列群組。背景 Tuple 行動器（TM）進程會定期喚醒，以壓縮 CLOSED 差異數據列群組。建議您在元組移動器落後時使用 REORGANIZE 。 REORGANIZE 可以更積極地壓縮數據列群組。
若要壓縮所有 OPEN 和 CLOSED 數據列群組，請參閱 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) 本節中的選項。

針對 SQL Server 中的數據行存放區索引（從 SQL Server 2016 （13.x）開始）和 Azure SQL 資料庫，REORGANIZE在線執行下列額外的重組優化：

當 10% 或更多資料列已經以邏輯方式刪除時，會實際將資料列從資料列群組移除。已刪除的位元組會在實體媒體上回收。例如，如果 1 百萬個數據列的壓縮數據列群組已刪除 100,000 個數據列，SQL Server 會移除已刪除的數據列，並使用 900k 個數據列重新壓縮數據列群組。將刪除的資料列移除可以節省儲存空間。
可合併一或多個壓縮的資料列群組，將每個資料列群組的資料列數目最多提高至 1,048,576 個資料列的上限。例如，如果您大容量導入 5 批 102,400 個數據列，則會收到 5 個壓縮的數據列群組。如果您執行 REORGANIZE，這些數據列群組會合併成大小為512,000個數據列的1個壓縮資料列群組。這是假設沒有任何目錄大小或記憶體限制的情況。
對於邏輯刪除 10% 或更多資料列的數據列群組，SQL Server 會嘗試將此數據列群組與一或多個數據列群組結合。例如，資料列群組 1 壓縮了 500,000 個資料列，而資料列群組 21 則壓縮了達到數目上限的 1,048,576 個資料列。資料列群組 21 中刪除了 60% 的資料列，剩下 409,830 個資料列。 SQL Server 會合併這兩個資料列群組，以壓縮一個包含 909,830 個資料列的新資料列群組。

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

適用資於料行存放區索引。

適用於：SQL Server (從 SQL Server 2016 (13.x) 開始) 和 Azure SQL Database

COMPRESS_ALL_ROW_GROUPS 提供強制 OPEN 或 CLOSED 差異數據列群組進入數據行存放區的方式。使用此選項時，不需要重建資料行存放區索引來清空差異資料列群組。此功能和其他移除與合併重組功能結合之後，可讓它在大部分情況下都不再需要重建索引。

ON 會強制所有數據列群組進入數據行存放區，而不論大小和狀態為何（CLOSED 或 OPEN）。
OFF 強制所有數據 CLOSED 列群組進入數據行存放區。

如需詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

SET （ <set_index 選項> [ ,... n ]

在不重建或重新組織索引的情況下，指定索引選項。 SET 無法為停用的索引指定。

PAD_INDEX = { ON | OFF }

指定索引填補。預設值為 OFF。

ON

FILLFACTOR 指定的可用空間百分比會套用到索引的中繼層級頁面上。如果未FILLFACTOR同時PAD_INDEX指定，ON則會使用儲存在 sys.indexes 中的填滿因數值。
OFF 或未指定 fillfactor

填入中繼層級頁面至接近填滿的程度。這會保留至少足以容納一個資料列的空間，且該資料列具有索引所能擁有的大小上限 (以中繼頁面的索引鍵組為基礎)。

如需詳細資訊，請參閱 CREATE INDEX。

FILLFACTOR = fillfactor

指定百分比，以表示 Database Engine 在索引建立或變更期間應該將每個索引頁面的分葉層級填滿的程度。 [fillfactor] 必須是 1 到 100 之間的整數值。預設值是 0。填滿因數值 0 和 100 在各方面都是一樣的。

只有在最初建立或重建索引時，才適用明確的 FILLFACTOR 設定。資料庫引擎不會動態保留頁面中空白空間的指定百分比。如需詳細資訊，請參閱 CREATE INDEX。

若要檢視填滿因數設定，請使用 sys.indexes 中的 fill_factor。

重要

利用 FILLFACTOR 值來建立或變更叢集索引時，會影響資料所佔用的儲存空間量，因為資料庫引擎在建立叢集索引時，會轉散發資料。

SORT_IN_TEMPDB = { ON | OFF }

指定是否要將排序結果儲存在中 tempdb。預設值為 OFF Azure SQL 資料庫超大規模資料庫除外。針對超大規模資料庫中的所有索引重建作業，不論指定的選項為何，除非使用可繼續的索引重新編製作業，否則 SORT_IN_TEMPDB 一律會是 ON。

ON

用來建置索引的中繼排序結果會儲存在中 tempdb。如果 tempdb 位於與用戶資料庫不同的磁碟集上，這可能會縮短建立索引所需的時間。不過，這會增加建立索引時所使用的磁碟空間量。
OFF

中繼排序結果會儲存在與用來儲存索引相同的資料庫中。

如果不需要排序作業，或排序可在記憶體中執行，即忽略 SORT_IN_TEMPDB 選項。

如需詳細資訊，請參閱索引的 SORT_IN_TEMPDB 選項。

IGNORE_DUP_KEY = { ON | OFF }

指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。 IGNORE_DUP_KEY 選項只適用於在建立或重新編製索引之後所發生的插入作業。預設值為 OFF。

開啟

當重複的索引鍵值插入唯一索引時，就會出現警告訊息。只有違反唯一性條件約束的數據列會失敗。
OFF

當重複的索引鍵值插入唯一索引時，就會出現錯誤訊息。整個 INSERT 作業都會回復。

IGNORE_DUP_KEY 無法在檢視表、非唯一索引、XML 索引、空間索引和篩選索引上建立的索引設定為 ON 。

若要檢視 IGNORE_DUP_KEY，請使用 sys.indexes。

在與舊版本相容的語法中，WITH IGNORE_DUP_KEY 相當於 WITH IGNORE_DUP_KEY = ON。

STATISTICS_NORECOMPUTE = { ON | OFF }

針對與指定索引相關的統計數據，停用或啟用自動統計數據更新選項 AUTO_STATISTICS_UPDATE。預設值為 OFF。

ON

重新建置索引之後，就會停用自動統計數據更新。
OFF

重新建置索引之後，就會啟用自動統計數據更新。

若要還原自動更新統計數據，請將設定STATISTICS_NORECOMPUTE為 OFF，或在沒有子句的情況下NORECOMPUTE執行UPDATE STATISTICS。

警告

如果您停用統計數據的自動更新，可能會防止查詢優化器為涉及數據表的查詢挑選最佳執行計劃。您應該謹慎使用這個選項，而且只能由合格的資料庫管理員使用。

此設定不會防止在重建作業期間使用索引相關統計數據的完整掃描自動更新。

STATISTICS_INCREMENTAL = { ON | OFF }

適用於：SQL Server 2014 （12.x）和更新版本，以及 Azure SQL 資料庫

當為時 ON，所建立的統計數據是每個數據分割統計數據。當為時 OFF，會卸除統計數據樹狀目錄，而 SQL Server 會重新計算統計數據。預設值為 OFF。

如果不支援每個分割區統計數據，則會忽略選項並產生警告。針對下列統計資料類型，不支援累加統計資料：

建立統計資料時，所使用的索引未與基底資料表進行分割區對齊
在可用性群組可讀取次要資料庫上建立的統計資料
在唯讀資料庫上建立的統計資料
在篩選的索引上建立的統計資料
在檢視表上建立的統計資料
在內部資料表上建立的統計資料
使用空間索引或 XML 索引建立的統計資料

ONLINE = { ON | OFF } <在套用至 rebuild_index_option 時>

指定在索引作業期間，查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。預設值為 OFF。

若為 XML 索引或空間索引，則僅 ONLINE = OFF 支援，如果 ONLINE 設定為 ON 錯誤，則為。

重要

並非所有 Microsoft SQL Server 版本都提供線上編製索引作業。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

ON

索引作業期間不會保留長期資料表鎖定。在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。這使得基礎資料表和索引的查詢或更新能夠繼續運作。作業開始時，會在來源對象上短暫保留共用（S）鎖定。作業結束時，如果建立非叢集索引，則會短暫保留來源上的 S 鎖定。建立或卸除叢集索引時，以及重建叢集或非叢集索引時，會取得架構修改（Sch-M）鎖定。 ONLINE 在本機暫存表上建立索引時，無法設定為 ON 。
OFF

在索引作業期間會套用資料表鎖定。建立、重建或卸除叢集索引、空間索引或 XML 索引的離線索引作業，或是重建或卸除非叢集索引的離線索引作業，將會取得資料表的結構描述修改 (Sch-M) 鎖定。這可防止所有使用者在作業期間存取基礎資料表。建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。這可防止更新基礎表，但允許讀取作業，例如 SELECT 語句。

如需詳細資訊，請參閱在線執行索引作業。

您可以在線上重建索引，其中包括全域暫存資料表的索引，但下列情況除外：

XML 索引
本機暫存資料表上的索引
在檢視上的初始唯一叢集索引
資料行存放區索引
叢集索引 (如果基礎資料表包含 LOB 資料類型 (image、ntext、text) 及空間類型)
varchar(max) 和 varbinary(max) 資料行不得為索引的一部分。在 SQL Server (從 SQL Server 2012 (11.x) 開始) 和 Azure SQL Database 中，當資料表包含 varchar(max) 或 varbinary(max) 資料行時，可以使用 ONLINE 選項來建立或重建包含其他資料行的叢集索引。當基底資料表包含 varchar(max) 或 varbinary(max) 資料行時，Azure SQL Database 不允許 ONLINE 選項

如需詳細資訊，請參閱線上索引作業如何運作。

下列 XEvent 與在線索引重建有關 ALTER TABLE ... SWITCH PARTITION 。

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

在討論串引作業的現有 XEvent progress_report_online_index_operation 包含 partition_number 與 partition_id。

RESUMABLE = { ON | OFF}

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

指定線上索引作業是否為可繼續的作業。

開啟

索引作業為可繼續的作業。
OFF

索引作業不是可繼續的作業。

MAX_DURATION = 時間 [ MINUTES ] 搭配 `RESUMABLE = ON` 使用（需要 `ONLINE = ON`）

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

指出可繼續的線上索引作業在暫停之前的執行時間 (以分鐘為單位指定的一個整數值)。

重要

如需可在線執行之索引作業的詳細資訊，請參閱在線索引作業的指導方針。

注意

數據行存放區索引不支援可繼續的在線索引重建。

ALLOW_ROW_LOCKS = { ON | OFF }

指定是否允許資料列鎖定。預設值為 ON。

開啟

當存取索引時，允許資料列鎖定。資料庫引擎會決定使用資料列鎖定的時機。
OFF

不會使用資料列鎖定。

ALLOW_PAGE_LOCKS = { ON | OFF }

指定是否允許頁面鎖定。預設值為 ON。

開啟

當您存取索引時，允許頁面鎖定。資料庫引擎會決定使用頁面鎖定的時機。
OFF

不會使用頁面鎖定。

注意

當設定為 OFF時ALLOW_PAGE_LOCKS，無法重新組織索引。

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用於：SQL Server 2019 （15.x）和更新版本，以及 Azure SQL 資料庫

指定是否要最佳化最後一頁的插入競爭。預設值為 OFF。如需詳細資訊，請參閱循序索引鍵。

MAXDOP = max_degree_of_parallelism

在索引作業期間，覆寫 max degree of parallelism 設定選項。如需詳細資訊，請參閱設定平行處理原則的最大程度（伺服器組態選項）。使用 MAXDOP 來限制平行計劃執行中使用的處理器數目。最大值是 64 個處理器。

重要

MAXDOP雖然選項在所有 XML 索引的語法上都受到支援，但空間索引或主要 XML 索引目前ALTER INDEX只使用單一處理器。

max_degree_of_parallelism 可以是：

1：隱藏平行計劃產生。
>1：將平行索引作業中使用的處理器數目上限限製為指定的數位。
0 （預設值）：根據目前的系統工作負載，使用實際處理器數目或更少數目。

如需詳細資訊，請參閱設定平行索引作業。

注意

SQL Server 的所有版本都無法使用平行索引作業。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

適用於：SQL Server (從 SQL Server 2016 (13.x) 開始)

如果是磁碟數據表，延遲會指定狀態差異數據列群組 CLOSED 必須保留在差異數據列群組中的最小分鐘數，SQL Server 才能將它壓縮成壓縮的數據列群組。由於磁碟數據表不會追蹤個別數據列的插入和更新時間，因此 SQL Server 會將延遲套用至處於狀態的差異 CLOSED 數據列群組。

預設值是 0 分鐘。

如需何時使用 COMPRESSION_DELAY的建議，請參閱開始使用數據行存放區進行即時作業分析。

DATA_COMPRESSION

針對指定的索引、分割區編號或分割區範圍指定資料壓縮選項。選項如下：

無

不會壓縮索引或指定的資料分割。這不適用於資料行存放區索引。
ROW

使用資料列壓縮來壓縮索引或指定的分割區。這不適用於資料行存放區索引。
PAGE

使用頁面壓縮來壓縮索引或指定的分割區。這不適用於資料行存放區索引。
COLUMNSTORE

適用於：SQL Server 2014 （12.x）和更新版本，以及 Azure SQL 資料庫

只適用於資料行存放區索引，包括非叢集資料行存放區索引和叢集資料行存放區索引。 COLUMNSTORE 指定以選項壓縮的索引或指定的 COLUMNSTORE_ARCHIVE 分割區。還原數據時，會繼續使用用於所有數據行存放區索引的數據行存放區壓縮進行壓縮。
COLUMNSTORE_ARCHIVE

適用於：SQL Server 2014 （12.x）和更新版本，以及 Azure SQL 資料庫

只適用於資料行存放區索引，包括非叢集資料行存放區索引和叢集資料行存放區索引。 COLUMNSTORE_ARCHIVE 進一步將指定的分割區壓縮為較小的大小。這可用於封存，或是其他需要較小儲存體，而且可負擔更多時間來儲存和擷取的狀況。

如需壓縮的詳細資訊，請參閱資料壓縮 (機器翻譯)。

XML_COMPRESSION

適用於：SQL Server 2022 (16.x) 和更新版本、Azure SQL 資料庫，以及 Azure SQL 受控執行個體。

針對包含一或多個 xml 資料類型資料行的指定索引，指定 XML 壓縮選項。選項如下：

開啟

使用 XML 壓縮來壓縮索引或指定的分割區。
OFF

不會壓縮索引或指定的資料分割。

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

指定套用 DATA_COMPRESSION 或 XML_COMPRESSION 設定的分割區。如果未分割索引，自 ON PARTITIONS 變數會產生錯誤。如果未提供 ON PARTITIONS 子句，DATA_COMPRESSION 或 XML_COMPRESSION 選項會套用到資料分割索引的所有分割區。

可以使用下列方式來指定 <partition_number_expression>：

提供分割區的編號，例如：ON PARTITIONS (2)。
為數個個別分割區提供以逗號分隔的分割區編號，例如：ON PARTITIONS (1, 5)。
同時提供範圍和個別分割區：ON PARTITIONS (2, 4, 6 TO 8)。

<range> 可以指定為以 TO 一字分隔的分割區編號，例如：ON PARTITIONS (6 TO 8)。

若要為不同的分割區設定不同類型的資料壓縮，請指定 DATA_COMPRESSION 選項一次以上，例如：

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)
);

您也可以多次指定 XML_COMPRESSION 選項，例如：

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <在套用至 single_partition_rebuild_index_option 時>

指定基礎資料表的索引或索引分割區可以在線上重建或離線重建。如果執行 REBUILD ... ONLINE = ON，這個資料表中的資料可以在索引作業期間用於查詢和資料修改。預設值為 OFF。

ON

索引作業期間不會保留長期資料表鎖定。在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。啟動索引重建且在線上索引重建結束時於資料表上需要結構描述修改 (Sch-S) 鎖定的時候，在資料表上需要結構描述穩定性 (Sch-M) 鎖定。雖然這兩個中繼資料鎖定的持續時間是短暫的，尤其是 Sch-M 鎖定必須等候所有封鎖交易完成。在等候期間，Sch-M 鎖定會在存取相同資料表時，封鎖等待在這個鎖定之後的所有其他交易。

注意

線上索引重建可以設定 low_priority_lock_wait 選項，請參閱 WAIT_AT_LOW_PRIORITY 與線上索引作業。
OFF

在索引作業期間會套用資料表鎖定。這可防止所有使用者在作業期間存取基礎資料表。

RESUME

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

繼續以手動方式暫停或因失敗而暫停的索引作業。

MAX_DURATION 搭配使用 RESUMABLE = ON

可繼續的線上索引作業在繼續之後的執行時間 (以分鐘為單位指定的整數值)。時間到期之後，如果可繼續的作業仍在執行中，就會暫停作業。
WAIT_AT_LOW_PRIORITY與和ONLINE = ON搭配RESUMABLE = ON使用。

在暫停之後繼續線上索引重建時，必須等候這個資料表的封鎖作業。 WAIT_AT_LOW_PRIORITY 表示在線索引重建作業會等候低優先順序鎖定，讓其他作業在在線索引建置作業等候時繼續。省略 WAIT_AT_LOW_PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。如需詳細資訊，請參閱 WAIT_AT_LOW_PRIORITY。

PAUSE

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

暫停可繼續的線上索引重建作業。

ABORT

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

中止已宣告為可繼續的執行中或已暫停的索引作業。您必須明確地執行 ABORT 命令，以終止可繼續的索引重建作業。失敗或暫停可繼續的索引作業不會終止其執行；相反地，會使作業進入無限期暫停狀態。

備註

ALTER INDEX 無法用來重新分割索引，或將其移到另一個檔案群組。您不能利用這個陳述式來修改索引定義，例如新增或刪除資料行，或變更資料行順序。請搭配 DROP_EXISTING 子句來使用 CREATE INDEX，以執行這些作業。

未明確指定選項時，會套用目前的設定。例如，如果未 FILLFACTOR 在 REBUILD 子句中指定設定，則會在重建程序期間使用儲存在系統目錄中的填滿因數值。若要檢視目前的索引選項設定，請使用 sys.indexes。

ONLINE、MAXDOP 和 SORT_IN_TEMPDB 的值並未儲存在系統目錄中。除非索引陳述式中另有指定，否則會使用選項的預設值。

在多重處理器的電腦上，ALTER INDEX REBUILD 也如同其他查詢一樣，會自動使用更多處理器來執行與修改索引相關的掃描和排序作業。當您執行 ALTER INDEX REORGANIZE時，若沒有 LOB_COMPACTION， 平行處理原則 值的最大程度就是單個線程作業。如需詳細資訊，請參閱設定平行索引作業。

重要

如果索引所在的檔案群組離線或設定為唯讀，則無法重新組織或重建索引。指定關鍵詞 ALL 且一或多個索引位於離線或唯讀檔案群組中時，語句會失敗。

重建索引

重建索引會卸除和重新建立索引。這會移除片段；根據指定的或現有的填滿因數設定壓縮頁面來收回磁碟空間，以及重新排序連續頁面中的索引資料列。指定時 ALL ，數據表上的所有索引都會卸除，並在單一交易中重建。不需要事先卸除外部索引鍵條件約束。當重建含有 128 個 (含) 以上範圍的索引時，資料庫引擎會延遲實際的頁面解除配置及其相關聯的鎖定，直到認可交易之後。

如需詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

重新組織索引

重新組織索引所用的系統資源最少。它會實際重新排序分葉層級的頁面，使它們由左至右符合分葉節點的邏輯順序，以重新組織資料表和檢視表之叢集和非叢集索引的分葉層級。重新組織也會壓縮索引頁面。壓縮是以現有填滿因數值為基礎。

當指定 ALL 時，會重新組織資料表的叢集和非叢集關聯式索引及 XML 索引。指定時 ALL，會套用某些限制，請參閱 ALL 本文之 Arguments 區段中的定義。

如需詳細資訊，請參閱優化索引維護以改善查詢效能並減少資源耗用量。

重要

對於具有已排序叢集數據行存放區索引的 Azure Synapse Analytics 數據表， ALTER INDEX REORGANIZE 不會重新排序數據。若要重新排序資料，請使用 ALTER INDEX REBUILD。

停用索引

停用索引可防止使用者存取索引，如果是叢集索引，則可防止使用者存取基礎資料表的資料。索引定義會保留在系統目錄中。停用檢視的非叢集索引或叢集索引時，會實際刪除索引資料。停用叢集索引可防止存取資料，資料仍會保留在 B 型樹狀目錄中，但不進行維護，直到卸除或重建索引為止。若要檢視已啟用或已停用索引的狀態，請查詢 sys.indexes 目錄檢視中的 is_disabled 資料行。

注意

檔使用一般參考索引的 B 型樹狀結構一詞。在數據列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。這不適用於記憶體優化數據表上的數據行存放區索引或索引。如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

如果資料表在異動複寫發行集中，您便無法停用關聯於主索引鍵資料行的任何索引。複寫需要這些索引。若要停用索引，您必須先從發行集中卸除資料表。如需詳細資訊，請參閱發行資料和資料庫物件。

使用 ALTER INDEX REBUILD 陳述式或 CREATE INDEX WITH DROP_EXISTING 陳述式來啟用索引。重建已停用的叢集索引，無法使用設定為 ON的選項來執行ONLINE。如需詳細資訊，請參閱停用索引和條件約束。

設定選項

您可以設定指定索引的選項 ALLOW_ROW_LOCKS、 ALLOW_PAGE_LOCKS、 OPTIMIZE_FOR_SEQUENTIAL_KEY、 IGNORE_DUP_KEY和 STATISTICS_NORECOMPUTE ，而不需重建或重新組織該索引。修改的值會立即套用在索引上。若要檢視這些設定，請使用 sys.indexes。如需詳細資訊，請參閱設定索引選項。

資料列和頁面鎖定選項

如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，當您存取索引時，允許資料列、頁面和資料表層級的鎖定。資料庫引擎會選擇適當的鎖定，且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，當您存取索引時，只允許資料表層級的鎖定。

如果在 ALL 設定數據列或頁面鎖定選項時指定，則設定會套用至所有索引。當基礎資料表是堆積時，會依照下列方式來套用設定：

選項	詳細資料
`ALLOW_ROW_LOCKS = ON or OFF`	套用在堆積和任何相關聯的非叢集索引上。
`ALLOW_PAGE_LOCKS = ON`	套用在堆積和任何相關聯的非叢集索引上。
`ALLOW_PAGE_LOCKS = OFF`	完整套用在非叢集索引上。這表示在非叢集索引上，不允許所有頁面鎖定。在堆積上，不允許的鎖定只有頁面的共用 (S)、更新 (U) 和獨佔 (X) 鎖定。資料庫引擎仍能取得意圖頁面鎖定 (IS、IU 或 IX)，供內部使用。

線上索引作業

重建索引並將 ONLINE 選項設定為 ON時，基礎對象、數據表和相關聯的索引可用於查詢和數據修改。您也可以在線上重建位於單一分割區之索引的一部分。獨占數據表鎖定只會在變更程序期間保留短時間。

索引一律是在線上重新組織。這個過程不會長期保留鎖定，因此，不會封鎖執行中的查詢或更新。

只有在執行下列作業時，才可以在相同的數據表或數據表分割區上執行並行在線索引作業：

建立多個非叢集索引。
在相同資料表上重新組織不同的索引。
在重建相同資料表的非重疊索引時，重新組織不同的索引。

同時執行的所有其他線上索引作業都會失敗。例如，您不能在相同資料表上，同時重建兩個或更多索引，或在相同資料表上重建現有索引時，建立新的索引。

如需詳細資訊，請參閱在線執行索引作業。

可繼續的索引作業

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

線上索引重建已使用 RESUMABLE = ON 選項指定為可繼續。

指定索引的中繼資料中不會保存 RESUMABLE 選項，並且僅適用於目前 DDL 陳述式的持續時間。因此，必須明確指定 RESUMABLE = ON 子句，才能啟用可繼續性。
針對 RESUMABLE = ON 選項或 low_priority_lock_wait 選項，支援 MAX_DURATION 選項。
- MAX_DURATION for RESUMABLE 選項會指定重建索引的時間間隔。使用此時間之後，索引重建會暫停或完成其執行。使用者可決定何時可以繼續已暫停索引的重建。 MAX_DURATION 的時間 (以分鐘為單位) 必須大於 0 分鐘，並少於或等於一週 (7 * 24 * 60 = 10080 分鐘)。索引作業長時間暫停可能會影響特定數據表上的 DML 效能，以及資料庫磁碟容量，因為索引（原始索引和新建立的磁碟）都需要磁碟空間，而且必須在 DML 作業期間更新。如果 MAX_DURATION 省略 option，索引作業會繼續執行，直到其完成或發生失敗為止。
- low_priority_lock_wait 引數選項可讓您決定索引作業在 Sch-M 鎖定上遭到封鎖時可繼續作業的方式。
重新執行具有相同參數的原始 ALTER INDEX REBUILD 陳述式，就會繼續已暫停的索引重建作業。您也可以執行 ALTER INDEX RESUME 陳述式，繼續已暫停的索引重建作業。
可繼續的索引不支援 SORT_IN_TEMPDB = ON 選項
具 RESUMABLE = ON 的 DDL 命令無法在明確交易內部執行 (不能是 BEGIN TRAN ... COMMIT 區塊的一部分)。
只有已暫停的索引作業才能繼續。
繼續暫停的索引作業時，您可以將值變更 MAXDOP 為新的值。如果在 MAXDOP 繼續暫停的索引作業時未指定，則會取得最後 MAXDOP 一個值。 MAXDOP如果未針對索引重建作業指定選項，則會採用預設值。
若要立即暫停索引作業，您可以停止進行中的命令 (Ctrl-C)，或執行 ALTER INDEX PAUSE 命令或 KILL <session_id> 命令。命令暫停之後，就可以使用 RESUME 選項繼續命令。
ABORT 命令會終止裝載原始索引重建的工作階段，並中止索引作業
可繼續的索引重建不需要額外的資源，除了
- 保留建立索引所需的額外空間，包括索引暫停的時間
- 可防止進行任何 DDL 修改的 DDL 狀態
準刪除清除會在索引暫停階段執行，但在索引執行期間暫停。已針對可繼續的索引重建作業停用下列功能
- RESUMABLE = ON 不支援重建已停用的索引
- ALTER INDEX REBUILD ALL 命令
- ALTER TABLE 使用索引重建
- 具 RESUMABLE = ON 的 DDL 命令無法在明確交易內部執行 (不能是 BEGIN TRAN ... COMMIT 區塊的一部分)
- 重建索引，其已計算或 TIMESTAMP 數據行做為索引鍵數據行。
如果基表包含 LOB 資料行可繼續叢集索引重建，則此作業的啟動需要 Sch-M 鎖定

注意

DDL 命令會執行，直到完成、暫停或失敗為止。如果命令暫停，則會發出錯誤，指出作業已暫停，且索引建立未完成。您可以從 sys.index_resumable_operations 取得目前索引狀態的詳細資訊。和以前一樣，如果發生失敗，也會發出錯誤。

WAIT_AT_LOW_PRIORITY 與線上索引作業

適用於：SQL Server 2014 （12.x）和更新版本，以及 Azure SQL 資料庫

low_priority_lock_wait 語法允許指定 WAIT_AT_LOW_PRIORITY 行為。 WAIT_AT_LOW_PRIORITY 只能搭配 ONLINE = ON 使用。

為了執行線上索引重建的 DDL 陳述式，特定資料表上執行的所有使用中封鎖交易都必須完成。當線上索引重建執行時，它會封鎖這個資料表上準備開始執行的所有新交易。雖然在線索引重建鎖定的持續時間很短，但等候指定數據表上所有開啟的交易完成並封鎖新交易開始，可能會大幅影響輸送量，導致工作負載變慢或逾時，並大幅限制基礎表的存取。

WAIT_AT_LOW_PRIORITY 選項可讓 DBA 管理線上索引重建所需的結構描述穩定性 (Sch-S) 鎖定和結構描述修改 (Sch-M) 鎖定，並且允許他們從兩個選項中選取其中一項。無論是哪一種情況，如果在等候時間 MAX_DURATION = n [minutes] 期間沒有封鎖活動，線上索引重建會立即執行而不等候，並且 DDL 陳述式會完成。

WAIT_AT_LOW_PRIORITY 表示在線索引重建作業會等候低優先順序鎖定，讓其他作業在在線索引建置作業等候時繼續。省略 WAIT AT LOW PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

MAX_DURATION = time [ MINUTES ]

執行 DDL 命令時，在線索引重建鎖定會以低優先順序等候的等候時間（以分鐘為單位指定的整數值）。如果作業在一段時間內遭到 MAX_DURATION 封鎖，則會執行指定的 ABORT_AFTER_WAIT 動作。 MAX_DURATION 時間一律以分鐘為單位，而且可以省略該字 MINUTES 。

ABORT_AFTER_WAIT = [ NONE |SELF |封鎖程式 ]

無

繼續等候一般 (標準) 優先權的鎖定。
SELF

結束目前正在執行的線上索引重建 DDL 作業，但不採取任何動作。選項 SELF 不能與 0 搭配 MAX_DURATION 使用。
BLOCKERS

終止封鎖線上索引重建 DDL 作業的所有使用者交易，讓作業可以繼續。 BLOCKERS 選項需要登入具有 ALTER ANY CONNECTION 權限。

空間索引的限制

當您重建空間索引時，在索引作業期間無法使用基礎使用者資料表，因為空間索引會持有結構描述鎖定。

在 PRIMARY KEY 該數據表的數據行上定義空間索引時，無法修改用戶數據表中的條件約束。若要變更 PRIMARY KEY 條件約束，請先卸除數據表的每個空間索引。修改 PRIMARY KEY 條件約束之後，您可以重新建立每個空間索引。

在單一分割區重建作業中，您不能指定任何空間索引。但是，您可以在完整分割區重建中指定空間索引。

若要變更空間索引特定的選項 (例如 BOUNDING_BOX 或 GRID)，您可以使用指定 DROP_EXISTING = ON 的 CREATE SPATIAL INDEX 陳述式，或是卸除此空間索引並建立新的索引。如需範例，請參閱 CREATE SPATIAL INDEX。

資料壓縮

如需數據壓縮的詳細資訊，請參閱數據壓縮。

若要評估變更 PAGE 和 ROW 壓縮如何影響數據表、索引或分割區，請使用 sp_estimate_data_compression_savings 預存程式。

下列限制適用於分割區索引：

使用 ALTER INDEX ALL ... 時，您無法在資料表具有非對齊索引時變更單一分割區的壓縮設定。
ALTER INDEX <index> ... REBUILD PARTITION ... 語法會重新編製此索引的指定分割區。
ALTER INDEX <index> ... REBUILD WITH ... 語法會重建此索引的所有分割區。

統計資料

當您針對資料表執行 ALTER INDEX ALL ... 時，只會更新與索引相關聯的統計資料。針對資料表 (而非索引) 所建立的自動或手動統計資料不會進行更新。

權限

若要執行 ALTER INDEX，至少需要資料表或檢視表的 ALTER 權限。

版本注意事項

SQL Database 不會使用檔案群組和 Filestream 選項。
SQL Server 2012 （11.x）之前無法使用資料行存放區索引。
從 2017 SQL Server 2017 (14.x) 和 Azure SQL Database 開始，可以使用可繼續的索引作業。

基本語法範例

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

範例：數據行存放區索引

這些範例適用於資料行存放區索引。

A. REORGANIZE 示範

此範例會示範 ALTER INDEX REORGANIZE 命令的運作方式。會建立一個擁有多個資料列群組的資料表，然後示範 REORGANIZE 如何合併資料列群組。

-- 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 TRANSACTION

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 選項來以平行處理方式插入資料列。從 SQL Server 2016 （13.x）開始， INSERT INTO 使用時 TABLOCK ，作業可以平行執行。

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

執行此命令以查看 OPEN 差異數據列群組。資料列群組的數目取決於平行處理原則的程度。

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

執行此命令，強制所有數據CLOSEDOPEN列群組進入數據行存放區。

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

再次執行此命令，您會看到較小的數據列群組合並成一個壓縮的數據列群組。

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

B. 將關閉的差異資料列群組壓縮到資料行存放區中

這個範例會使用 REORGANIZE 選項，將每個 CLOSED 差異數據列群組壓縮到數據行存放區中，做為壓縮的數據列群組。這並非必要，但當 Tuple-mover 無法快速壓縮 CLOSED 數據列群組時，這非常有用。

您可以在 AdventureWorksDW2022 範例資料庫中執行這兩個範例。

此範例會在所有分割區上執行 REORGANIZE 。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

此範例會在特定分割區上執行 REORGANIZE 。

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. 將所有開啟和關閉的差異資料列群組壓縮到資料行存放區中

適用於：SQL Server (從 SQL Server 2016 (13.x) 開始) 和 Azure SQL Database

命令 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) 會將每個 OPEN 和 CLOSED 差異數據列群組壓縮到數據行存放區中，做為壓縮的數據列群組。這會清空差異存放區，並將所有資料列強制壓縮到資料行存放區。這在執行許多插入作業之後特別有用，因為這些作業會將資料列儲存在一或多個差異資料行群組中。

REORGANIZE 結合數據列群組，以填滿最多數據列群組數目 <= 1,024,576。因此，當您壓縮所有 OPEN 和 CLOSED 數據列群組時，最後不會包含許多只有少數數據列的壓縮數據列群組。您可以將資料列群組盡量填滿，以縮小壓縮的大小並增進查詢效能。

下列範例使用 AdventureWorksDW2022 資料庫。

本範例會將所有 OPEN 和 CLOSED 差異數據列群組移至數據行存放區索引。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

本範例會將所有 OPEN 和 CLOSED 差異數據列群組移至特定數據分割的數據行存放區索引。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. 線上重組資料行存放區索引

不適用於：SQL Server 2012 （11.x）和 SQL Server 2014 （12.x）。

從 SQL Server 2016 （13.x）開始， REORGANIZE 會比將差異數據列群組壓縮到數據行存放區還要多。它也會執行線上重組。首先，它會在資料列群組中 10% 或更多資料列已遭到刪除時，實際移除已刪除的資料列，以縮小資料行存放區大小。然後，它會合併資料列群組以構成較大的資料列群組，每個資料列群組最多可包含 1,024,576 個資料列。所有變更的資料列群組都會重新壓縮。

注意

從 SQL Server 2016 （13.x）開始，在大部分情況下，重建數據行存放區索引已不再需要，因為 REORGANIZE 實際移除已刪除的數據列和合併數據列群組。選項會將 COMPRESS_ALL_ROW_GROUPS 所有 OPEN 或 CLOSED 差異數據列群組強制到資料行存放區中，這些資料行存放區先前只能透過重建來完成。 REORGANIZE 在在線併發生在背景中，因此查詢可以在作業發生時繼續。

下列範例會 REORGANIZE 執行，藉由實際移除已從數據表中邏輯刪除的數據列，以及合併數據列群組，來重組索引。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. 離線重建叢集資料行存放區索引

適用於：SQL Server (從 SQL Server 2012 (11.x) 開始)

提示

從 2016 SQL Server 2016 (13.x) 和 Azure SQL Database 開始，我們建議針對資料行存放區索引使用 ALTER INDEX REORGANIZE 而不是 ALTER INDEX REBUILD。

注意

在 SQL Server 2012 （11.x）和 SQL Server 2014 （12.x）中， REORGANIZE 僅用於將數據列群組壓縮 CLOSED 到數據行存放區。若要執行重組作業並將所有差異資料列群組強制移動到資料行存放區，重建索引是唯一的方式。

此範例說明如何重建叢集資料行存放區索引，並將所有差異資料列群組強制移動到資料行存放區。第一個步驟是在包含叢集資料行存放區索引的 AdventureWorksDW2022 資料庫中準備 FactInternetSales2 資料表，並插入前四個資料行的資料。

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;

結果會顯示一個 OPEN 數據列群組，這表示 SQL Server 會在關閉資料列群組並將數據移至數據行存放區之前，等候新增更多數據列。下一個陳述式會重建叢集資料行存放區索引，這會將所有資料列強制移動到資料行存放區中。

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

語句的結果會顯示數據列群組為 COMPRESSED，這表示數據列群組的數據SELECT行區段現在會壓縮並儲存在數據行存放區中。

F. 離線重建叢集資料行存放區索引的分割區

適用於：SQL Server 2012 (11.x) 和更新版本

若要重建大型叢集資料行存放區索引的分割區，請使用 ALTER INDEX REBUILD 與分割區選項。這個範例會重建分割區 12。從 SQL Server 2016 (13.x) 開始，我們建議將 REBUILD 取代為 REORGANIZE。

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. 變更叢集資料行存放區索引以使用封存壓縮

不適用於：SQL Server 2012 （11.x）

您可以選擇使用 COLUMNSTORE_ARCHIVE 資料壓縮選項，進一步減少叢集數據行存放區索引的大小。這項功能非常適合用於想要保存在較便宜儲存裝置上的較舊資料。建議您只在不常存取的數據上使用這個值，因為解壓縮速度比一般 COLUMNSTORE 壓縮慢。

下列範例會重建叢集資料行存放區索引來使用封存壓縮，然後示範如何移除封存壓縮。最終結果只會使用數據行存放區壓縮。

首先，建立具有叢集資料行存放區索引的資料表，以準備範例。然後，使用封存壓縮進一步壓縮資料表。

--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);
GO

此範例會移除封存壓縮，並且只使用資料行存放區壓縮。

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

範例：資料列存放區索引

A. 重建索引

下列範例會在 AdventureWorks2022 資料庫的 Employee 資料表上重建單一索引。

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. 在資料表上重建所有索引以及指定選項

下列範例會指定關鍵字 ALL。這會重建與 AdventureWorks2022 資料庫中 Production.Product 資料表相關聯的所有索引。指定三個選項。

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

下列範例會加入包括低優先權鎖定選項的 ONLINE 選項，並加入資料列壓縮選項。

適用於：SQL Server 2014 （12.x）和更新版本，以及 Azure SQL 資料庫

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. 重新組織具有 LOB 壓縮的索引

下列範例會重新組織 AdventureWorks2022 資料庫中的單一叢集索引。由於索引在分葉層級中包含 LOB 資料類型，因此，這個陳述式也會壓縮包含大型物件資料的所有頁面。因為預設值為 ON，所以不需要指定 WITH (LOB_COMPACTION = ON) 選項。

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

D. 設定索引上的選項

下列範例會設定 AdventureWorks2022 資料庫中 AK_SalesOrderHeader_SalesOrderNumber 索引的幾個選項。

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

E. 停用索引

下列範例會停用 AdventureWorks2022 資料庫中 Employee 資料表的非叢集索引。

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. 停用條件約束

下列範例會PRIMARY KEY藉由停用資料庫中的PRIMARY KEYAdventureWorks2022索引來停用條件約束。基礎 FOREIGN KEY 表的條件約束會自動停用，並顯示警告訊息。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

結果集會傳回這則警告訊息。

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. 啟用條件約束

下列範例會啟用範例 F 中已停用的 PRIMARY KEY 和 FOREIGN KEY 條件約束。

藉 PRIMARY KEY 由重建 PRIMARY KEY 索引來啟用條件約束。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

FOREIGN KEY然後會啟用條件約束。

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. 重建資料分割索引

下列範例會重建 AdventureWorks2022 資料庫中分割區索引 5 的單一分割區，分割區編號是 IX_TransactionHistory_TransactionDate。分割區 5 在 ONLINE=ON 重建，而且低優先權鎖定的 10 分鐘等候時間會分別套用至索引重建作業取得的每一個鎖定。如果在此時間無法取得鎖定來完成索引重建，則會由於 ABORT_AFTER_WAIT = SELF 導致重建作業陳述式本身中止。

適用於：SQL Server 2014 （12.x）和更新版本，以及 Azure SQL 資料庫

-- 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. 變更索引的壓縮設定

下列範例會在非分割資料列存放區資料表上重建索引。

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

J. 使用 XML 壓縮變更索引的設定

適用於：SQL Server 2022 (16.x) 和更新版本、Azure SQL 資料庫，以及 Azure SQL 受控執行個體。

下列範例會在非分割資料列存放區資料表上重建索引。

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

如需更多數據壓縮範例，請參閱數據壓縮。

K. 線上可繼續的索引重建

適用於：SQL Server 2017 （14.x）和更新版本，以及 Azure SQL 資料庫

以下範例說明如何使用線上可繼續的索引重建。

使用 MAXDOP = 1執行在線索引重建作為可繼續的作業。在索引作業暫停之後，再次執行相同的命令，會自動繼續索引重建作業。

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

執行在線索引重建作為可繼續的作業，並將 MAX_DURATION 設定為 240 分鐘。

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

暫停執行中的可繼續的線上索引重建。

ALTER INDEX test_idx on test_table PAUSE;

針對執行為可繼續作業的索引重建繼續在線索引重建，並指定設定為4的新值 MAXDOP 。

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

繼續索引線上重建 (以可繼續的方式執行) 的線上索引重建作業。設定 MAXDOP 為 2，將執行索引的運行時間設定為可繼續 240 分鐘，如果鎖定上封鎖索引，請等候 10 分鐘，並在之後終止所有封鎖程式。

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));

中止正在執行或暫停的可繼續索引重建作業。

ALTER INDEX test_idx on test_table ABORT;

共用方式為

ALTER INDEX (Transact-SQL)

Syntax

引數

index_name

ALL

database_name

schema_name

table_or_view_name

REBUILD [ WITH （ <rebuild_index_option> [ ,... n ] ] ]

PARTITION

DISABLE

REORGANIZE 資料列存放區索引

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

REORGANIZE 資料行存放區索引

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

SET （ <set_index 選項> [ ,... n ]

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 | OFF } <在套用至 rebuild_index_option 時>

RESUMABLE = { ON | OFF}

MAX_DURATION = 時間 [ MINUTES ] 搭配 RESUMABLE = ON 使用 （需要 ONLINE = ON）

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

DATA_COMPRESSION

XML_COMPRESSION

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

ONLINE = { ON | OFF } <在套用至 single_partition_rebuild_index_option 時>

RESUME

PAUSE

ABORT

備註

重建索引

重新組織索引

停用索引

設定選項

資料列和頁面鎖定選項

線上索引作業

可繼續的索引作業

WAIT_AT_LOW_PRIORITY 與線上索引作業

MAX_DURATION = time [ MINUTES ]

ABORT_AFTER_WAIT = [ NONE |SELF |封鎖程式 ]

空間索引的限制

資料壓縮

統計資料

權限

版本注意事項

基本語法範例

範例：數據行存放區索引

A. REORGANIZE 示範

B. 將關閉的差異資料列群組壓縮到資料行存放區中

C. 將所有開啟和關閉的差異資料列群組壓縮到資料行存放區中

D. 線上重組資料行存放區索引

E. 離線重建叢集資料行存放區索引

F. 離線重建叢集資料行存放區索引的分割區

G. 變更叢集資料行存放區索引以使用封存壓縮

範例：資料列存放區索引

A. 重建索引

B. 在資料表上重建所有索引以及指定選項

C. 重新組織具有 LOB 壓縮的索引

D. 設定索引上的選項

E. 停用索引

F. 停用條件約束

G. 啟用條件約束

H. 重建資料分割索引

I. 變更索引的壓縮設定

J. 使用 XML 壓縮變更索引的設定

K. 線上可繼續的索引重建

相關內容

意見反應

其他資源

MAX_DURATION = 時間 [ MINUTES ] 搭配 `RESUMABLE = ON` 使用（需要 `ONLINE = ON`）