ALTER INDEX (Transact-SQL)

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

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

主題連結圖示Transact-SQL 語法慣例

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

-- Syntax for Azure Synapse Analytics 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 ] ) ]
    | 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 }
}

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

引數

index_name

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

ALL

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

搭配此作業使用關鍵字 ALL 如果資料表有一個或多個下列項目,便告失敗
REBUILD WITH ONLINE = ON XML 索引

空間索引

資料行存放區索引:適用于:從 2012 SQL Server 2012 (11.x) ) 和 Azure SQL Database 開始SQL Server (
REBUILD PARTITION = partition_number 未分割索引、XML 索引、空間索引或停用的索引
REORGANIZE ALLOW_PAGE_LOCKS 設定為 OFF 的索引
REORGANIZE PARTITION = partition_number 未分割索引、XML 索引、空間索引或停用的索引
IGNORE_DUP_KEY = ON XML 索引

空間索引

資料行存放區索引:適用于:從 2012 SQL Server 2012 (11.x) ) 和 Azure SQL Database 開始SQL Server (
ONLINE = ON XML 索引

空間索引

資料行存放區索引:適用于:從 2012 SQL Server 2012 (11.x) ) 和 Azure SQL Database 開始SQL Server (
RESUMABLE = ON All 關鍵字不支援可繼續的索引。

適用于:從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

警告

如需有關可以在線上執行之索引作業的詳細資訊,請參閱線上索引作業的指導方針

如果設定 PARTITION = partition_number來指定 ALL,便會對齊所有索引。 這表示它們會根據相等的資料分割函數進行分割。 搭配 PARTITION 子句使用 ALL 時,會重建或重新組織含有相同 partition_number 的所有索引分割區。 如需有關分割區索引的詳細資訊,請參閱< Partitioned Tables and Indexes>。

database_name

資料庫的名稱。

schema_name

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

table_or_view_name

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

SQL Database支援三部分名稱格式database_name。[schema_name]當database_name為目前資料庫或database_name為 tempdb,且table_or_view_name開頭為 #時.table_or_view_name。

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

適用于:從 SQL Server 2012 (11.x) ) 和 Azure SQL Database 開始SQL Server (

指定將利用相同的資料行、索引類型、唯一性屬性和排序次序來重建索引。 這個子句相當於 DBCC DBREINDEXREBUILD 啟用停用的索引。 除非指定了 ALL 關鍵字,否則重建叢集索引不會重建相關聯的非叢集索引。 如果未指定索引選項,則會套用儲存在 sys.indexes 中的現有索引選項值。 對於值未儲存在 sys.indexes 的任何索引選項中,選項的引數定義中會套用預設值。

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

REBUILD如果資料庫復原模式設定為大容量日誌或簡單,則作業可以最低限度記錄。

注意

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

針對資料行存放區索引,作業 REBUILD

  • 不使用排序次序。
  • 在發生 時 REBUILD 取得資料表或分割區上的獨佔鎖定。 即使使用 NOLOCK、讀取認可快照集隔離 (RCSI) ,或 SI () ,資料仍「離線」且無法使用 REBUILD
  • 將資料重新壓縮到資料行存放區。 資料行存放區索引的兩個複本在進行時 REBUILD 存在。 REBUILD完成時,SQL Server會刪除原始的資料行存放區索引。

如需詳細資訊,請參閱 重新組織與重建索引

PARTITION

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

PARTITION = ALL 會重建所有分割區。

警告

您可以對包含超過 1,000 個分割區的資料表,建立及重建不以資料表為準的索引,但不予支援。 此做法可能會導致在作業期間效能降低或耗用過多記憶體。 Microsoft 建議當分割區數超過 1,000 個時,才使用以資料表為準的索引。

partition_number

要重建或重新組織之分割索引的分割區編號。 partition_number 是一個可以參考變數的常數運算式。 這些包括使用者定義型別變數或函數和使用者定義函數,但無法參考 Transact-SQL 語句。 partition_number必須存在,否則陳述式將會失敗。

WITH (< single_partition_rebuild_index_option >)

SORT_IN_TEMPDBMAXDOPDATA_COMPRESSIONXML_COMPRESSION 是單一分割 (PARTITION = partition_number) 區時 REBUILD 可以指定的選項。 無法在單一資料分割 REBUILD 作業中指定 XML 索引。

DISABLE

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

REORGANIZE 資料列存放區索引

對於資料列存放區索引,REORGANIZE 會指定重新組織索引分葉層級。 REORGANIZE 作業:

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

注意

例如,使用 ALTER INDEX REORGANIZE 明確交易 (例如, ALTER INDEX 在) 內 BEGIN TRAN ... COMMIT/ROLLBACK ,而不是預設隱含交易模式時,的 REORGANIZE 鎖定行為會變得更嚴格,可能會造成封鎖。 如需隱含交易的詳細資訊,請參閱 SET IMPLICIT_TRANSACTIONS (Transact-SQL)

如需詳細資訊,請參閱 重新組織與重建索引

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

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

LOB_COMPACTION = ON

  • 指定壓縮包含這些大型物件 (LOB) 資料類型的所有頁面: imagetextNtextVarchar (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-mover (TM) 程式會定期喚醒,以壓縮 CLOSED 差異資料列群組。 當 tuple-mover 進度落後時,我們建議使用 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,這些資料列群組將會合併成 1 個包含 512,000 個資料列的壓縮資料列群組。 這是假設沒有任何目錄大小或記憶體限制的情況。

  • 對於邏輯上已刪除 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 2016 (13.x) ) 和 Azure SQL Database 開始SQL Server (

COMPRESS_ALL_ROW_GROUPS 可用來將開啟或關閉的差異資料列群組強制移動到資料行存放區中。 使用此選項時,不需要重建資料行存放區索引來清空差異資料列群組。 此功能和其他移除與合併重組功能結合之後,可讓它在大部分情況下都不再需要重建索引。

  • ON 會將所有資料列群組強制移動到資料行存放區中,無論其大小和狀態 (關閉或開啟) 為何。
  • OFF 則會將所有關閉的資料列群組強制移動到資料行存放區中。

如需詳細資訊,請參閱 重新組織與重建索引

SET ( < set_index 選項 > [ ,... n] )

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

PAD_INDEX = { ON | OFF }

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

開啟

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

未指定 OFF 或fillfactor

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

如需詳細資訊,請參閱 CREATE FULLTEXT CATALOG (TRANSACT-SQL)

FILLFACTOR = fillfactor

指定百分比,指出 Database Engine 在索引建立或變更期間,每個索引頁面的分葉層級應該有多滿。 fillfactor的值必須是介於 1 到 100 的整數值。 預設值是 0。 填滿因數值 0 和 100 在各方面都是一樣的。

只有在第一次建立或重建索引時,才會套用明確的 FILLFACTOR 設定。 Database Engine 不會動態保留頁面中的指定空白空間百分比。 如需詳細資訊,請參閱 CREATE FULLTEXT CATALOG (TRANSACT-SQL)

若要檢視填滿因數設定,請在 中使用 fill_factorsys.indexes

重要

建立或改變具有 FILLFACTOR 值的叢集索引會影響資料佔用的儲存空間量,因為 Database Engine 會在建立叢集索引時重新發佈資料。

SORT_IN_TEMPDB = { ON | OFF }

指定是否將排序結果儲存在 tempdb 中。 預設值是關閉,但 Azure SQL Database 超大規模資料庫例外。 對於超大規模資料庫中的所有索引重建作業, SORT_IN_TEMPDB 一律為 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 }

指定是否要重新計算散發統計資料。 預設值為 OFF。

開啟
過期的統計資料不會自動重新計算。

OFF
啟用自動統計資料更新。

若要還原自動統計資料更新,請將 設定 STATISTICS_NORECOMPUTE 為 OFF,或不使用 NORECOMPUTE 子句執行 UPDATE STATISTICS

重要

停用散發統計資料的自動重新計算,可防止查詢最佳化工具取得與資料表有關之查詢的最佳執行計畫。

STATISTICS_INCREMENTAL = { ON | OFF }

適用于:從 SQL Server 2014 (12.x) ) 和 Azure SQL Database 開始SQL Server (

若設定為 ON,所建立的統計資料會以每個資料分割統計資料為依據。 當 OFF時,會卸載統計資料樹狀結構,並SQL Server重新計算統計資料。 預設值為 OFF

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

  • 使用未與基表對齊之索引建立的統計資料
  • 在可用性群組可讀取次要資料庫上建立的統計資料
  • 在唯讀資料庫上建立的統計資料
  • 在篩選的索引上建立的統計資料
  • 在檢視表上建立的統計資料
  • 在內部資料表上建立的統計資料
  • 使用空間索引或 XML 索引建立的統計資料

ONLINE = { ON |OFF } < 適用于 rebuild_index_option>

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

如果是 XML 索引或空間索引,則只支援 ONLINE = OFF,而如果將 ONLINE 設定為 ON,將會引發錯誤。

重要

Microsoft SQL Server的每個版本都無法使用線上索引作業。 如需 SQL Server 版本所支援的功能清單,請參閱:

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

OFF
資料表鎖定會在索引作業期間套用。 建立、重建或卸除叢集索引、空間索引或 XML 索引的離線索引作業,或是重建或卸除非叢集索引的離線索引作業,將會取得資料表的結構描述修改 (Sch-M) 鎖定。 這可防止所有使用者在作業期間存取基礎資料表。 建立非叢集索引的離線索引作業會取得資料表的共用 (S) 鎖定。 這可避免對基礎資料表進行更新,但仍可執行讀取作業,如 SELECT 陳述式。

如需詳細資訊,請參閱 Perform Index Operations Online

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

  • XML 索引
  • 本機暫存資料表上的索引
  • 在檢視上的初始唯一叢集索引
  • 資料行存放區索引
  • 叢集索引 (如果基礎資料表包含 LOB 資料類型 (imagentexttext) 及空間類型)
  • 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 選項

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

RESUMABLE = { ON |OFF}

適用于:從 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

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

開啟
索引作業為可繼續的作業。

OFF
索引作業無法繼續。

MAX_DURATION = 與 (搭配 RESUMABLE = ON 使用的時間[MINUTES] 需要 ONLINE = ON)

適用于:從 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

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

重要

如需有關可以在線上執行之索引作業的詳細資訊,請參閱線上索引作業的指導方針

注意

資料行存放區索引不支援可繼續的線上索引重建。

ALLOW_ROW_LOCKS = { ON | OFF }

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

開啟
當存取索引時,允許資料列鎖定。 Database Engine 會決定何時使用資料列鎖定。

OFF
不會使用資料列鎖定。

ALLOW_PAGE_LOCKS = { ON | OFF }

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

開啟
當您存取索引時,允許頁面鎖定。 Database Engine 會決定何時使用頁面鎖定。

OFF
不會使用頁面鎖定。

注意

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用于:從 SQL Server 2019 (15.x) ) 和 Azure SQL 資料庫開始SQL Server (

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

MAXDOP = max_degree_of_parallelism

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

重要

雖然所有 XML 索引的語法支援 MAXDOP 選項,但針對空間索引或主要 XML 索引, ALTER INDEX 目前只使用單一處理器。

max_degree_of_parallelism 可以是:

1
隱藏平行計畫的產生。

>1
將平行索引作業所用的最大處理器數目限制為指定的數目。

0 (預設值)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。

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

注意

SQL Server的每個版本都無法使用平行索引作業。 如需 SQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能

COMPRESSION_DELAY = { 0 | duration [Minutes] }

適用于:從 SQL Server 2016 (13.x) ) 開始SQL Server (

至於磁碟資料表,延遲會指定關閉 狀態下的差異資料列群組,必須在差異資料列群組中至少保留多少分鐘的時間,然後 SQL Server 才能將它壓縮到壓縮的資料列群組。 因為磁碟資料表不會追蹤個別資料列的插入和更新時間,因此 SQL Server 會將這段延遲時間套用於關閉狀態下的差異資料列群組。

預設值是 0 分鐘。

如需 COMPRESSION_DELAY 的使用時機建議,請參閱開始使用資料行存放區進行即時作業分析

DATA_COMPRESSION

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


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

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

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

COLUMNSTORE

適用于:從 SQL Server 2014 (12.x) ) 和 Azure SQL Database 開始SQL Server (

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

COLUMNSTORE_ARCHIVE

適用于:從 SQL Server 2014 (12.x) ) 和 Azure SQL Database 開始SQL Server (

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

如需與壓縮有關的詳細資訊,請參閱資料壓縮

XML_COMPRESSION

適用于:SQL Server 2022 (16.x) 及更新版本,以及 Azure SQL Database Preview。

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

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

OFF
不壓縮索引或指定的分割區。

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

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

注意

從 SQL Server 2022 (16.x) 和 Azure SQL Database Preview 開始,才能使用 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

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

注意

線上索引重建可以設定 low_priority_lock_wait 選項,請參閱 使用線上索引作業WAIT_AT_LOW_PRIORITY

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

RESUME

適用于:從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

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

與 搭配使用的MAX_DURATION RESUMABLE = ON

可繼續的線上索引作業在繼續之後的執行時間 (以分鐘為單位指定的整數值)。 一旦時間到期,如果繼續作業仍在執行中,就會暫停可繼續的作業。

WAIT_AT_LOW_PRIORITY 搭配 RESUMABLE = ONONLINE = 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

適用于:從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

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

ABORT

適用于:從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

中止已宣告為可繼續的執行中或已暫停的索引作業。 您必須明確執行 ABORT 命令,以終止可繼續的索引重建作業。 失敗或暫停可繼續的索引作業不會終止其執行;相反地,它會讓作業處於無限暫停狀態。

備註

ALTER INDEX 無法用來重新分割索引,或將它移至不同的檔案群組。 此語句無法用來修改索引定義,例如新增或刪除資料行或變更資料行順序。 請搭配 DROP_EXISTING 子句來使用 CREATE INDEX,以執行這些作業。

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

MAXDOPSORT_IN_TEMPDB 的值 ONLINE 不會儲存在系統目錄中。 除非索引陳述式中另有指定,否則會使用選項的預設值。

在多重處理器的電腦上,ALTER INDEX REBUILD 也如同其他查詢一樣,會自動使用更多處理器來執行與修改索引相關的掃描和排序作業。 當您執行 ALTER INDEX REORGANIZE 時,不論是否設定了 LOB_COMPACTION,max degree of parallelism 值都是單一執行緒作業。 如需詳細資訊,請參閱 設定平行索引作業

重要

如果索引所在的檔案群組離線或設為唯讀,便無法重新組織或重建索引。 當指定了 ALL 關鍵字,且有一個或多個索引在離線或唯讀檔案群組中,陳述式會失敗。

重建索引

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

如需詳細資訊,請參閱 重新組織與重建索引

重新組織索引

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

當指定 ALL 時,會重新組織資料表的叢集和非叢集關聯式索引及 XML 索引。 當指定 ALL 時,適用某些限制,請參閱本文<引數>一節中的 ALL 定義。

如需詳細資訊,請參閱 重新組織與重建索引

重要

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

停用索引

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

注意

SQL Server檔通常會使用 B 型樹狀結構一詞來參考索引。 在資料列存放區索引中,SQL Server實作 B+ 樹狀結構。 這不適用於資料行存放區索引或記憶體內部資料存放區。 如需詳細資訊,請檢閱SQL Server索引架構和設計指南

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

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

設定選項

您可以在不重建或重新組織指定之索引的情況下,設定這個索引的 ALLOW_ROW_LOCKSALLOW_PAGE_LOCKSOPTIMIZE_FOR_SEQUENTIAL_KEYIGNORE_DUP_KEYSTATISTICS_NORECOMPUTE 選項。 修改的值會立即套用在索引上。 若要檢視這些設定,請使用 sys.indexes 。 如需詳細資訊,請參閱 設定索引選項

資料列和頁面鎖定選項

如果 ALLOW_ROW_LOCKS = ONALLOW_PAGE_LOCK = ON,當您存取索引時,允許資料列、頁面和資料表層級的鎖定。 Database Engine 會選擇適當的鎖定,並可將鎖定從資料列或頁面鎖定呈報到資料表鎖定。

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

如果指定 ALL,且設定了資料列或頁面鎖定,便會將這些設定套用至所有索引上。 當基礎資料表是堆積時,會依照下列方式來套用設定:

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

線上索引作業

當重建索引且 ONLINE 選項設為 ON 時,查詢和資料修改可以使用基礎物件、資料表和相關聯的索引。 您也可以在線上重建位於單一分割區之索引的一部分。 在改變過程中,只會在非常短的時間內,保留獨佔的資料表鎖定。

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

只有在執行下列動作時,您才能在相同資料表或資料表分割區上執行並行的線上索引作業:

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

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

如需詳細資訊,請參閱 Perform Index Operations Online

可繼續的索引作業

適用于:從 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

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

  • 選項 RESUMABLE 不會保存在指定索引的中繼資料中,而且只適用于目前 DDL 語句的持續時間。 因此,必須明確指定 RESUMABLE = ON 子句,才能啟用可繼續性。

  • MAX_DURATION選項或 low_priority_lock_wait 選項支援 RESUMABLE = ON 選項。

    • RESUMABLE 選項的 MAX_DURATION 可指定重建索引時的時間間隔。 使用此時間之後,索引重建就會暫停或完成執行。 使用者可決定何時可以繼續已暫停索引的重建。 ) 分鐘 (的時間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 Database 開始SQL Server (

語法 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 | BLOCKERS ]


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

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

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

空間索引限制

當您重建空間索引時,基礎使用者資料表在索引作業期間無法使用,因為空間索引會保留架構鎖定。

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

在單一資料分割重建作業中,您無法指定任何空間索引。 但是,您可以在完整分割區重建中指定空間索引。

若要變更空間索引的特定選項,例如 BOUNDING_BOXGRID ,您可以使用 CREATE SPATIAL INDEX 指定 DROP_EXISTING = ON 的語句,或卸載空間索引並建立新的索引。 如需範例,請參閱 CREATE SPATIAL INDEX (Transact-SQL)

資料壓縮

如需資料壓縮的詳細資訊,請參閱 資料壓縮

若要評估變更 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不使用檔案群組和檔案資料流程選項。
  • 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 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 選項來以平行處理方式插入資料列。 從 SQL Server 2016 (13.x) 開始,當使用 TABLOCK 時, INSERT INTO 作業可以平行執行。

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

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

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

執行此命令以將所有關閉和開啟的資料列群組強制移動到資料行存放區中。

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 資料列群組的速度夠快時,這非常有用。

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

此範例會在所有分割區上執行 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 2016 (13.x) ) 和 Azure SQL Database 開始SQL Server (

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

REORGANIZE 可合併資料列群組,讓資料列群組中的資料列數目最高達到資料列 <= 1,024,576 的數目上限。 因此當您壓縮所有開啟和關閉的資料列群組時,不會產生裡面只有幾個資料列的大量已壓縮資料列群組。 您可以將資料列群組盡量填滿,以縮小壓縮的大小並增進查詢效能。

下列範例使用 AdventureWorksDW2016 資料庫。

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

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

本範例會將所有 OPEN AND 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) 。

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

注意

從 2016 SQL Server 2016 (13.x) 開始,在大部分情況下,重建資料行存放區索引已不再需要,因為 REORGANIZE 實際上會移除已刪除的資料列併合並資料列群組。 COMPRESS_ALL_ROW_GROUPS 選項會將所有開啟或關閉的差異資料列群組強制移動到資料行存放區中,之前只能使用重建執行這項作業。 REORGANIZE 在線上且會在背景執行,因此可以在作業執行時繼續進行查詢。

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

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

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

適用于:從 2012 SQL Server (11.x) ) 開始SQL Server (

提示

從 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 資料列群組壓縮到資料行存放區。 若要執行重組作業並將所有差異資料列群組強制移動到資料行存放區,重建索引是唯一的方式。

此範例說明如何重建叢集資料行存放區索引,並將所有差異資料列群組強制移動到資料行存放區。 第一個步驟會準備資料庫中具有叢集資料行存放區索引的 AdventureWorksDW 資料表 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;

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

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

適用于:SQL Server (從 2012 SQL Server 2012 (11.x) ) 開始

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

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. 重建索引

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

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. 重建資料表上的所有索引並指定選項

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

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 Database 開始SQL Server (

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 壓縮重新組織索引

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

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

D. 在索引上設定選項

下列範例會設定 AdventureWorks2012 資料庫中 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. 停用索引

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

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. 停用條件約束

下列範例會藉由停用 AdventureWorks2012 資料庫中 PRIMARY KEY 索引來停用 PRIMARY KEY 條件約束。 基礎資料表的 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. 重建分割索引

下列範例會重建 AdventureWorks2012 資料庫中分割區索引 5 的單一分割區,分割區編號是 IX_TransactionHistory_TransactionDate。 分割區 5 已使用 ONLINE=ON 重建,且低優先順序鎖定的等候時間會分別套用至索引重建作業取得的每個鎖定。 如果在這段期間無法取得鎖定以完成索引重建,則因為 而中止重建作業語句本身。 ABORT_AFTER_WAIT = SELF

適用于:從 SQL Server 2014 (12.x) ) 和 Azure SQL Database 開始SQL Server (

-- 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 Database Preview。

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

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

如需更多資料壓縮範例,請參閱 資料壓縮

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

適用于:從 2017 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始SQL Server (

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

  1. 使用 MAXDOP = 1 執行線上索引重建作為可繼續的作業。

    ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON) ;
    
  2. 在索引作業暫停之後再次執行相同命令 (請參閱上述說明),會自動繼續索引重建作業。

  3. 以 MAX_DURATION 設為 240 分鐘 的可繼續作業方式,執行線上索引重建。

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

    ALTER INDEX test_idx on test_table PAUSE ;
    
  5. 繼續索引重建 (以指定新值將 MAXDOP 設為 4 之可繼續的作業方式執行) 的線上索引重建。

    ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4) ;
    
  6. 繼續索引線上重建 (以可繼續的方式執行) 的線上索引重建作業。 將 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)) ;
    
  7. 中止執行中或已暫停的可繼續的索引重建作業。

    ALTER INDEX test_idx on test_table ABORT ;
    

另請參閱