gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertDeze browser wordt niet meer ondersteund.
Upgrade naar Microsoft Edge om te profiteren van de nieuwste functies, beveiligingsupdates en technische ondersteuning.
Applies to:
SQL Server 2022 (16.x)
Azure SQL Database
Azure SQL Managed Instance
The resumable operation for online index creation and rebuild are already supported for SQL Server 2019, Azure SQL Database, and Azure SQL Managed Instance. The resumable operations allow index operations to be executed while the table is online (ONLINE=ON
) and also:
Pause and restart an index create or rebuild operation multiple times to fit a maintenance window
Recover from index creation or rebuild failures, such as database failovers or running out of disk space.
Enable truncation of transaction logs during an index creation or rebuild operation.
When an index operation is paused, both the original index and the newly created one require disk space and need to be updated during Data Manipulation Language (DML) operations.
The new extensions for SQL Server 2022, SQL Database, and SQL Managed Instance allow a resumable operation for the Data Definition Language (DDL) command ALTER TABLE ADD CONSTRAINT and adding a Primary or Unique Key. For more information on adding a Primary or Unique Key, see ALTER TABLE table_constraint.
Notitie
Resumable add table constraints apply only to PRIMARY KEY and UNIQUE KEY constraints. Resumable add table constraints is not supported for FOREIGN KEY constraints.
In previous versions of SQL Server, the ALTER TABLE ADD CONSTRAINT
operation can be executed with the ONLINE=ON
option. However, the operation may take many hours for a large table to complete, and can consume a great number of resources. There's also the possibility of failures or interruption during such execution. We've introduced resumable capabilities to ALTER TABLE ADD CONSTRAINT
for users to pause the operation during a maintenance window, or to restart it from where it was interrupted during an execution failure, without restarting the operation from the beginning.
The new resumable capability for ALTER TABLE ADD CONSTRAINT
supports the following customer scenarios:
Pause or resume running ALTER TABLE ADD CONSTRAINT
operation, such as pausing it for a maintenance window, and resuming the operation once the maintenance window is complete.
Resume ALTER TABLE ADD CONSTRAINT
operation after failovers and system failures.
Executing ALTER TABLE ADD CONSTRAINT
operation on a large table despite the small log size available.
Notitie
The resumable operation for ALTER TABLE ADD CONSTRAINT
requires the ALTER
command to be executed online (WITH ONLINE = ON
).
This feature is especially useful for large tables.
For information on the syntax used to enable resumable operations on a table constraint, see the syntax and options in ALTER TABLE (Transact-SQL).
A new clause WITH <resumable_options have been added to the current T-SQL syntax in ALTER TABLE (Transact-SQL).
The option RESUMABLE is new and has been added to the existing ALTER TABLE (Transact-SQL) syntax.
MAX_DURATION
= time [MINUTES] used with RESUMABLE = ON
(requires ONLINE = ON
). MAX_DURATION
indicates time (an integer value specified in minutes) that a resumable online add constraint operation is executed before being paused. If not specified, the operation continues until completion.
To pause, resume, or abort the resumable table constraint operation for ALTER TABLE ADD CONSTRAINT
, use the T-SQL syntax ALTER INDEX (Transact-SQL).
For resumable constraints the existing ALTER INDEX ALL command is used.
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 ALL ON <Table> PAUSE
ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]
MAX_DURATION
used with RESUMABLE=ON
WAIT_AT_LOW_PRIORITY
used with RESUMABLE=ON
and ONLINE = ON
WAIT_AT_LOW_PRIORITY
indicates that the add table constraint operation will wait for low priority locks, allowing other operations to proceed while the resumable operation is waiting. Omitting the WAIT_AT_LOW_PRIORITY
option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)
. For more information, see WAIT_AT_LOW_PRIORITY.ALTER INDEX ALL ON <Table> ABORT
ABORT
command to terminate a resumable constraint operation. Failure or pausing a resumable table constraint operation doesn't terminate its execution. Rather, it leaves the operation in an indefinite paused state.For more information on PAUSE
, RESUME
, and ABORT
options available for resumable operations, see ALTER INDEX (Transact-SQL).
To view the status for the resumable table constraint operation, use the view sys.index_resumable_operations.
Requires ALTER
permission on the table.
No new permissions for resumable ALTER TABLE ADD CONSTRAINT
are required.
Here are some examples on using resumable add table constraint operations.
Resumable ALTER TABLE
operation for adding a primary key clustered on column (a) with MAX_DURATION
of 240 minutes.
ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);
Resumable ALTER TABLE
operation for adding a unique constraint on two columns (a and b) with MAX_DURATION
of 240 minutes.
ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);
ALTER TABLE
operation for adding a primary key clustered being paused and resumed.
The table below shows two sessions (Session #1
and Session #2
) being executed chronologically using the following T-SQL statements. Session #1
executes a resumable ALTER TABLE ADD CONSTRAINT
operation creating a primary key on column Col1
. Session #2
checks the execution status for the running constraint. After some time, it pauses the reusable operation. Session #2
checks the status for the paused constraint. Finally, Session #1
resumes the paused constraint and Session #2
checks the status again.
Session #1 | Session #2 | ||||||
---|---|---|---|---|---|---|---|
Execute resumable add constraint ALTER TABLE TestConstraint ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1) WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30); |
|||||||
Check the constraint status SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Output showing the operation
|
|||||||
Pause the resumable constraint ALTER INDEX ALL ON TestConstraint PAUSE; |
|||||||
Error 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. |
|||||||
Check the constraint status SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Output showing the operation
|
|||||||
ALTER INDEX ALL ON TestConstraint RESUME; |
|||||||
Check the constraint status SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Output showing the operation
|
Once the operation is completed, execute the following T-SQL statement to check the constraint:
SELECT constraint_name, table_name, constraint_type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO
Here's the result set:
constraint_name | table_name | constraint_type |
---|---|---|
PK_Constraint | TestConstraint | PRIMARY KEY |
gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertTraining
Module
Een performant gegevensmodel ontwerpen in Azure SQL Database met Azure Data Studio - Training
Meer informatie over het maken van een gegevensmodel, tabellen, indexen, beperkingen en het gebruik van gegevenstypen met Azure Data Studio.
Certificering
Microsoft Certified: Azure Database Administrator Associate - Certifications
Beheer een SQL Server-databaseinfrastructuur voor cloud-, on-premises en hybride relationele databases met behulp van de relationele Microsoft PaaS-databaseaanbiedingen.
Documentatie
ALTER TABLE (Transact-SQL) - SQL Server
ALTER TABLE wijzigt een tabeldefinitie door kolommen en beperkingen te wijzigen, toe te voegen of te verwijderen. ALTER TABLE kan ook partities opnieuw toewijzen en opnieuw bouwen, of beperkingen en triggers uitschakelen en inschakelen.
Primaire sleutels maken in SQL Server - SQL Server
Definieer een primaire sleutel in de SQL Server Database Engine met behulp van SQL Server Management Studio of Transact-SQL.
Unieke beperkingen en controlebeperkingen - SQL Server
UNIEKE beperkingen en CHECK-beperkingen zijn twee typen beperkingen die kunnen worden gebruikt om gegevensintegriteit af te dwingen.