ALTER TABLE index_option (Transact-SQL)

文章
05/23/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

指定使用 ALTER TABLE 所建立之條件約束定義中的索引所能套用的一組選項。

如需索引選項的完整描述，請參閱 CREATE INDEX。

語法

{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [ MINUTES ]
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

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

引數

PAD_INDEX = { ON | OFF }

適用於：SQL Server 2008 (10.0.x) 和更新版本

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

ON

FILLFACTOR 指定的可用空間百分比會套用到索引的中繼層級頁面上。
OFF 或未指定 fillfactor

在中繼頁面上提供索引鍵組之後，中繼層級頁面容量的填滿程度會保留至少足以容納一個資料列的空間，且該資料列的大小上限是索引所能擁有的大小上限。

FILLFACTOR = fillfactor

適用於：SQL Server 2008 (10.0.x) 和更新版本

指定百分比，以表示資料庫引擎在索引建立或變更期間應該將每個索引頁的分葉層級填滿的程度。指定的值必須是 1 至 100 之間的整數值。預設值是 0。

注意

從各方面來說，填滿因數值 0 和 100 都相同。

IGNORE_DUP_KEY = { ON | OFF }

指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的回應類型。 IGNORE_DUP_KEY 選項只適用於在建立或重新編製索引之後所發生的插入作業。執行 CREATE INDEX、ALTER INDEX 或 UPDATE 時，這個選項沒有任何作用。預設值為 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。

警告

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

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

ALLOW_ROW_LOCKS = { ON | OFF }

適用於：SQL Server 2008 (10.0.x) 和更新版本

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

開啟

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

不會使用資料列鎖定。

ALLOW_PAGE_LOCKS = { ON | OFF }

適用於：SQL Server 2008 (10.0.x) 和更新版本

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

開啟

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

不會使用頁面鎖定。

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用於：SQL Server 2019 (15.x) 和更新版本

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

SORT_IN_TEMPDB = { ON | OFF }

適用於：SQL Server 2008 (10.0.x) 和更新版本

指定是否要將排序結果儲存在中 tempdb。預設值為 OFF。

ON

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

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

ONLINE = { ON | OFF }

適用於：SQL Server 2008 (10.0.x) 和更新版本

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

注意

無法在線建立唯一非叢集索引。這包括因為或 PRIMARY KEY 條件約束而建立的UNIQUE索引。

ON

索引作業期間不會保留長期資料表鎖定。在索引作業的主要階段期間，來源資料表上只保留意圖共用 (IS) 鎖定。這可使基礎資料表和索引的查詢或更新能夠進行。作業開始時，來源物件會保留一段短時間內的共享鎖定。在作業結束時，如果在建立非叢集索引時，會在來源上取得 S （Shared）鎖定;或在在線建立或卸除叢集索引，以及重建叢集或非叢集索引時，取得 Sch-M （架構修改）鎖定。雖然線上索引鎖定是短暫的中繼資料鎖定，尤其是 Sch-M 鎖定必須等候這個資料表上的所有封鎖交易完成。在等候期間，Sch-M 鎖定會在存取相同的資料表時，封鎖此鎖定之後所有其他等候的交易。 ONLINE 在本機暫存表上建立索引時，無法設定為 ON 。

注意

線上索引重建可設定本節稍後所述的 low_priority_lock_wait 選項。 low_priority_lock_wait 會在線上索引重建期間管理 S 和 Sch-M 鎖定優先順序。
OFF

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

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

注意

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

RESUMABLE = { ON | OFF}

適用於：SQL Server 2022 (16.x) 和更新版本

指定 ALTER TABLE ADD CONSTRAINT 作業是否為可繼續的作業。若為 ON，則新增資料表條件約束作業為可繼續的作業。當時 OFF，無法繼續加入數據表條件約束作業。預設為 OFF。當 RESUMABLE 選項設定為 ON 時，需要 ONLINE = ON 選項。

MAX_DURATION 與 RESUMABLE = ON 搭配使用時， ONLINE = ON表示在暫停之前執行可繼續的在線加入條件約束作業的時間（以分鐘為單位指定的整數值）。如果未指定，作業會繼續直到完成為止。 MAXDOP 也支援 RESUMABLE = ON 。

如需啟用和使用可繼續 ALTER TABLE ADD CONSTRAINT 作業的詳細資訊，請參閱可繼續的新增資料表條件約束。

MAXDOP = max_degree_of_parallelism

適用於：SQL Server 2008 (10.0.x) 和更新版本

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

max_degree_of_parallelism 可以是：

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

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

注意

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

DATA_COMPRESSION

適用於：SQL Server 2008 (10.0.x) 和更新版本

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

無

不會壓縮資料表或指定的資料分割。僅適用於資料列存放區資料表；不適用於資料行存放區資料表。
ROW

使用資料列壓縮來壓縮資料表或指定的分割區。僅適用於資料列存放區資料表；不適用於資料行存放區資料表。
PAGE

使用頁面壓縮來壓縮資料表或指定的分割區。僅適用於資料列存放區資料表；不適用於資料行存放區資料表。
COLUMNSTORE

適用於：SQL Server 2014 (12.x) 和更新版本

只適用於資料行存放區資料表。 COLUMNSTORE 指定使用選項解壓縮已壓縮的數據 COLUMNSTORE_ARCHIVE 分割。還原數據時， COLUMNSTORE 索引會繼續使用用於所有數據行存放區數據表的數據行存放區壓縮來壓縮。
COLUMNSTORE_ARCHIVE

適用於：SQL Server 2014 (12.x) 和更新版本

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

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

XML_COMPRESSION

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

指定資料表中任何 xml 資料類型資料行的 XML 壓縮選項。選項如下：

開啟

使用 xml 資料類型的資料行會壓縮。
OFF

使用 xml 資料類型的資料行不會壓縮。

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

適用於：SQL Server 2008 (10.0.x) 和更新版本

指定套用 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 選項一次以上，例如：

--For rowstore tables
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)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

在大多數情況下，重建索引也會重建資料分割索引的所有分割區。下列選項套用至單一分割區時，不會重建所有分割區。

SORT_IN_TEMPDB
MAXDOP
DATA_COMPRESSION
XML_COMPRESSION

low_priority_lock_wait

適用於：SQL Server 2014 (12.x) 和更新版本

或 SWITCH 在線索引重建會在此數據表沒有封鎖作業時立即完成。 WAIT_AT_LOW_PRIORITY表示如果SWITCH或在線索引重建作業無法立即完成，則會等候。作業會具有低優先順序鎖定，允許其他具有與 DDL 陳述式衝突之鎖定的作業執行。省略 WAIT AT LOW PRIORITY 選項相當於 WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)。

MAX_DURATION = time [ MINUTES ]

執行 DDL 命令時，必須取得或在線索引重建鎖定的等候時間（以分鐘 SWITCH 為單位指定的整數值）。 SWITCH或在線索引重建作業會嘗試立即完成。如果作業在一段時間內遭到 MAX_DURATION 封鎖，則會 ABORT_AFTER_WAIT 執行其中一個動作。 MAX_DURATION 時間一律以分鐘為單位，而且可以省略該字 MINUTES 。

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

NONE

SWITCH繼續或在線索引重建作業，而不變更鎖定優先順序（使用一般優先順序）。
SELF

SWITCH結束或在線索引重建 DDL 作業目前正在執行，而不採取任何動作。
BLOCKERS

終止目前 SWITCH 封鎖或在線索引重建 DDL 作業的所有使用者交易，讓作業可以繼續。

BLOCKERSALTER ANY CONNECTION需要許可權。

分享方式：

ALTER TABLE index_option (Transact-SQL)

語法

引數

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

SORT_IN_TEMPDB = { ON | OFF }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF}

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

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

<single_partition_rebuild__option>

low_priority_lock_wait

MAX_DURATION = time [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

意見反映

更多資源

分享方式：

ALTER TABLE index_option (Transact-SQL)

語法

引數

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

SORT_IN_TEMPDB = { ON | OFF }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF}

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

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

<single_partition_rebuild__option>

low_priority_lock_wait

MAX_DURATION = time [ MINUTES ]

ABORT_AFTER_WAIT = { NONE |SELF |BLOCKERS }

相關內容

意見反映

更多資源