分享方式:


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

適用於: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=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

ALTER INDEX ALL ON <Table> ABORT

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

若要進一步了解可繼續作業適用的 PAUSERESUMEABORT 選項,請參閱 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 #1Session #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_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43.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_textstate_descpercent_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_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING90.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

另請參閱