Resumable Operations for ALTER TABLE Constraints in SQL Server 2022

Introduction:

SQL Server 2022 introduces a powerful feature called resumable operations, which enables users to perform the addition of constraints while the table remains accessible. The resumable operation for online index creation and rebuild are already supported on SQL Server 2019 and this Resumable operation feature are only supported in the SQL Server Enterprise and Developer editions.

This blog post will focus on the resumable operations feature for ALTER TABLE constraints, specifically for adding primary or unique keys. We will explore the benefits of resumable operations, its syntax, and its limitations.

Benefits of Resumable Operations:

In previous versions of SQL Server, performing online index creation or adding constraints to large tables could be time-consuming and resource-intensive. Moreover, any interruptions or failures during the execution would require restarting the operation from scratch. However, with the resumable operations feature in SQL Server 2022, users can now pause and resume operations as needed, reducing the risk of data inconsistencies and improving efficiency. This feature is a game-changer for managing large tables and minimizing downtime.

Syntax:

To declare a resumable operation for adding primary or unique key constraints to a table, you can use the following syntax:

1. ALTER TABLE table_name ADD CONSTRAINT PK_name PRIMARY KEY CLUSTERED (column_name)                                                               WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

In the above syntax, the WITH RESUMABLE = ON clause indicates that the operation can be paused and resumed.

The optional MAX_DURATION clause allows you to specify the maximum duration, in minutes, before the operation is automatically paused.

Adjusting the MAXDOP option controls the degree of parallelism during the operation.

Let’s look at an example using T-SQL, here we are creating a Primary Key for the “Id” column on the “tblAuthors” table.

1. ALTER TABLE tblAuthors ADD CONSTRAINT PK_name PRIMARY KEY CLUSTERED (Id)                                                                        WITH (ONLINE = ON, MAXDOP =1, RESUMABLE = ON, MAX_DURATION = 1);

In the above query we have set the Max_Duration to 1 minute. So after one minute the execution will be automatically paused and will show the message like below.

Monitoring and Managing Resumable Operations:

Once a resumable operation for adding constraints is initiated, it can be useful to monitor the progress and manage the operation. SQL Server provides several commands to facilitate this process.

• To check the status of resumable operations, you can execute the following query:

1. SELECT * FROM sys.index_resumable_operations;

This query provides information about the current status, progress, and other details related to ongoing resumable operations in the database.

Below is the status of the addition of the Primary key process which we have initiated.

Here you can see the process is in paused state.

• To abort or cancel the add constraint operation, you can use the following command:

1. ALTER INDEX ALL ON  table_name ABORT;

Executing this command will halt the operation and remove any changes made during the operation. It’s important to note that this action cannot be undone, so caution should be exercised before using the ABORT command.

• In situations where you need to temporarily pause the add constraint operation, you can use the following command:

1. ALTER INDEX ALL ON  table_name PAUSE;

This command will suspend the operation, allowing you to free up resources or address any immediate concerns.

• To resume the operation, execute the following command:

1. ALTER INDEX ALL ON  table_name RESUME;

This command will resume the add constraint operation from where it was paused, ensuring continuity and progress.

Using the above resume command, resumed the addition of Primary Key in the tblAuthors table and it was resumed from where it was paused and completed successfully.

Limitations:

It’s essential to be aware of certain limitations when utilizing resumable operations for ALTER TABLE constraints:

ONLINE Option: The ONLINE option must be set to ON to enable resumable operations for adding constraints. This option ensures that the table remains accessible during the operation.

Edition Restrictions: The ONLINE option is only available in the Enterprise and Developer editions of SQL Server. Resumable operations for ALTER TABLE constraints are supported in the Enterprise and Developer editions of SQL Server 2022. Other editions may not have access to this feature. Ensure that you have the appropriate edition to leverage resumable operations effectively.

Constraints Restrictions: Resumable operations for ALTER TABLE constraints are currently limited to primary key and unique key constraints. Foreign key constraints do not support resumable operations in SQL Server 2022.

By understanding these limitations, you can effectively plan and execute resumable operations for adding constraints in SQL Server 2022 while optimizing resource usage and minimizing downtime.

Summary:

SQL Server 2022’s resumable operations feature introduces enhanced control and flexibility for performing online operations on large tables. By allowing the addition of primary and unique key constraints through resumable operations, users can reduce downtime, minimize resource consumption, and mitigate the impact of failures or interruptions. Make the most of this powerful feature to streamline your database management processes and improve overall efficiency.