可繼續的新增資料表條件約束

適用於：SQL Server 2022 (16.x) Azure SQL 資料庫 Azure SQL 受控執行個體

SQL Server 2019、Azure SQL Database 和 Azure SQL 受控執行個體均已支援線上索引建立和重建的可繼續作業。 可繼續作業允許在資料表 上線時執行索引作業 (ONLINE=ON)，還有：

• 配合維護期間，多次暫停和重新啟動索引建立或重建作業

• 從索引建立或重建失敗 (例如，資料庫容錯移轉或磁碟空間不足) 中復原。

• 在索引建立或重建作業期間，啟用交易記錄的截斷功能。

• 當索引作業暫停時，原始索引和新建立的索引都需要磁碟空間，而且必須在資料操作語言 (DML) 作業期間進行更新。

適用於 SQL Server 2022、SQL Database 和 SQL 受控執行個體的新延伸模組允許使用資料定義語言 (DDL) 命令 ALTER TABLE ADD CONSTRAINT 執行可繼續作業，並新增主要或唯一索引鍵。 如需新增主要或唯一索引鍵的詳細資訊，請參閱 ALTER TABLE table_constraint。

注意

可繼續的加入資料表條件約束僅適用於 PRIMARY KEY 和 UNIQUE KEY 條件約束。 FOREIGN KEY 條件約束不支援可繼續的新增資料表條件約束。

可繼續的作業

在舊版的 SQL Server 中，可以使用 ONLINE=ON 選項執行 ALTER TABLE ADD CONSTRAINT 作業。 但若是大型資料表，則此作業可能需要數小時的時間才能完成，而且會耗用大量資源。 在這類執行期間，也可能會發生失敗或中斷。 我們在 ALTER TABLE ADD CONSTRAINT 中加入了可繼續功能，讓使用者可以在維護期間暫停作業，或從執行失敗時中斷的位置重新啟動作業，而不需從頭開始重新啟動作業。

支援的案例

適用於 ALTER TABLE ADD CONSTRAINT 的新可繼續功能支援下列客戶案例：

• 暫停或繼續執行 ALTER TABLE ADD CONSTRAINT 作業，例如在維護期間暫停作業，並在維護期間完成後繼續作業。

• 容錯移轉和系統失敗之後繼續 ALTER TABLE ADD CONSTRAINT 作業。

• 儘管有小型記錄檔大小可用，仍可在大型資料表上執行 ALTER TABLE ADD CONSTRAINT 作業。

注意

適用於 ALTER TABLE ADD CONSTRAINT 的可繼續作業需要連線執行 ALTER 命令 (WITH ONLINE = ON)。

此功能對於大型資料表特別有用。

ALTER TABLE 的 T-SQL 語法

如需在資料表條件約束上啟用可繼續作業的語法資訊，請參閱 ALTER TABLE (Transact-SQL) 中的語法和選項。

ALTER TABLE 的備註

• 新子句 WITH <resumable_options 已新增到 ALTER TABLE (Transact-SQL) 的最新 T-SQL 語法中。

• RESUMABLE 是新選項且已新增到現有的 ALTER TABLE (Transact-SQL) 語法中。

• MAX_DURATION = time [MINUTES] 與 RESUMABLE = ON 一起使用 (需要 ONLINE = ON)。 MAX_DURATION 指出可繼續的線上新增條件約束作業在暫停之前的執行時間 (以分鐘為單位指定的一個整數值)。 如果未指定，作業會繼續直到完成為止。

ALTER INDEX 的 T-SQL 語法

若要使用 ALTER TABLE ADD CONSTRAINT 暫停、繼續或中止可繼續的資料表條件約束作業，請使用 T-SQL 語法 ALTER INDEX (Transact-SQL)。

針對可繼續的條件約束，使用現有的 ALTER INDEX ALL 命令。

ALTER INDEX ALL ON <table_name>  
      { RESUME [WITH (<resumable_index_options>,[...n])]
        | PAUSE
        | ABORT
      }
<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 } )  
}  

ALTER INDEX 的備註

ALTER INDEX ALL ON <Table> PAUSE

• 暫停正在執行的可繼續和線上新增資料表條件約束作業

ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]

• 繼續以手動方式暫停或因失敗而暫停的新增資料表條件約束作業。

MAX_DURATION 搭配使用 RESUMABLE=ON

• 可繼續的新增資料表條件約束作業在繼續之後的執行時間 (以分鐘為單位指定的整數值)。 時間到期之後，如果可繼續的作業仍在執行中，就會暫停作業。

WAIT_AT_LOW_PRIORITY 搭配 RESUMABLE=ON 和 ONLINE = 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。

ALTER INDEX ALL ON <Table> ABORT

• 中止正在執行或已暫停的新增資料表條件約束作業，此作業已宣告為可繼續作業。 必須以 ABORT 命令此明確執行中止作業，才能終止可繼續的條件約束作業。 失敗或暫停的可繼續資料表條件約束作業並不會終止其執行。 而是會讓作業處於無限暫停狀態。

若要進一步了解可繼續作業適用的 PAUSE 、 RESUME 和 ABORT 選項，請參閱 ALTER INDEX (Transact-SQL)。

檢視可繼續作業的狀態

若要檢視可繼續資料表條件約束作業的狀態，請使用檢視 sys.index_resumable_operations。

權限

必須具備資料表的ALTER權限。

可繼續的 ALTER TABLE ADD CONSTRAINT 不需任何新權限。

範例

以下是使用可繼續的新增資料表條件約束作業的一些範例。

範例 1

在資料行 (a) 上新增叢集主索引鍵的可繼續 ALTER TABLE 作業，MAX_DURATION 為 240 分鐘。

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

範例 2

可繼續的 ALTER TABLE 作業用來新增兩個資料行 (a 和 b) 的唯一條件約束，並使用 MAX_DURATION 設定時間上限 240 分數。

ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

範例 3

暫停並繼續用來新增主索引鍵叢集的 ALTER TABLE 作業。

下表顯示使用下列 T-SQL 陳述式，按時間順序執行的兩個工作階段 (Session #1 和 Session #2)。 Session #1 執行可繼續的 ALTER TABLE ADD CONSTRAINT 作業，在資料行 Col1 建立主索引鍵。 Session #2 檢查正在執行的條件約束的執行狀態。 一段時間後，它會暫停可重複使用的作業。 Session #2 檢查已暫停的條件約束的狀態。 最後，Session #1 繼續暫停的條件約束，且 Session #2 會再次檢查狀態。

工作階段 1	工作階段 2
執行可繼續的新增條件約束 ALTER TABLE TestConstraint ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1) WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
	檢查條件約束狀態 SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations;
顯示作業的輸出 sql_text state_desc percent_complete ALTER TABLE TestConstraint (...) RUNNING 43.552
	暫停可繼續的條件約束 ALTER INDEX ALL ON TestConstraint PAUSE;
錯誤 Msg 1219, Level 16, State 1, Line 6 Your session has been disconnected because of a high priority DDL operation. Msg 1750, Level 16, State 1, Line 6 Could not create constraint or index. See previous errors. Msg 0, Level 20, State 0, Line 5 A severe error occurred on the current command. The results, if any, should be discarded.
	檢查條件約束狀態 SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations;
顯示作業的輸出 sql_text state_desc percent_complete ALTER TABLE TestConstraint (...) 已暫停 65.339
ALTER INDEX ALL ON TestConstraint RESUME;
	檢查條件約束狀態 SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations;
顯示作業的輸出 sql_text state_desc percent_complete ALTER TABLE TestConstraint (...) RUNNING 90.238

作業完成後，請執行下列 T-SQL 陳述式來檢查條件約束：

SELECT constraint_name, table_name, constraint_type 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO

結果集如下：

constraint_name	table_name	constraint_type
PK_Constraint	TestConstraint	PRIMARY KEY

另請參閱

• 線上索引作業的指導方針
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option