Pause and Resume Creation of Table Constraints in SQL Server 2022

Problem

SQL Server 2022 has a new feature that allows you to pause and resume the creation of primary and unique constraints for a table. In this article, we look at how to use this new feature.

Solution

For smaller tables, adding a constraint can be fast but with large tables this process can take much longer, sometimes hours and the use of many resources. In previous versions, if you wanted to pause the process because of resource issues there was no option. The only option was to stop the creation and start over. Now in SQL Server 2022, you can pause and resume the process when adding a constraint.

In SQL Server 2022, this new option applies when you add a Primary Key to a table or a unique key using the ALTER TABLE command. Note: The ALTER TABLE command requires including the option WITH ONLINE = ON.

Tip Prerequisites

1. SQL Server 2022 installed
2. SSMS installed
3. Adventureworks database (for the last example)

Creating a Primary Key

Let’s look at an example using T-SQL. We will create a new table named salesorderdetailtest based on the [Sales].[SalesOrderDetail] Adventureworks table and using the SELECT INTO statement.

SELECT *
INTO dbo.salesorderdetailtest
FROM [Sales].[SalesOrderDetail]

Next, we will modify table dbo.salesorderdetailtest and add a constraint named PK_salesord3 which will be a primary key using the SalesOrderDetailID column.

ALTER TABLE dbo.salesorderdetailtest
ADD CONSTRAINT PK_salesord3 PRIMARY KEY CLUSTERED ([SalesOrderDetailID])
WITH (ONLINE = ON, MAXDOP = 3, RESUMABLE = ON, MAX_DURATION = 120);

•  ONLINE argument must be set to ON.
• MAXDOP = 3 is the MAX Degree of Parallelism used to limit the number of processors. In this case, three processors.
• MAX_DURATION in minutes which is the maximum duration that the constraint creation can be RESUMABLE.
•  RESUMABLE = ON is the new argument.

Common Errors and How to Troubleshoot

The following query will try to create a primary key named PK_salesord4:

ALTER TABLE dbo.salesorderdetailtest
ADD CONSTRAINT PK_salesord4 PRIMARY KEY CLUSTERED ([SalesOrderID])
WITH (ONLINE = ON, MAXDOP = 3, RESUMABLE = ON, MAX_DURATION = 120);

The error message is the following:

This error occurs because SalesOrderID contains some duplicate values. However, the add constraint operation is now in resumable status.

To check the status, you can use the sys.index_resumable_operations system view. This view allows you to check the indexes and the status:

SELECT * FROM sys.index_resumable_operations

We can see the state of the index is PAUSED.

If we try to alter the index, we get the following error message:

ALTER TABLE dbo.salesorderdetailtest
DROP CONSTRAINT PK_salesord4

Here is the error.

So, if you have indexes in resumable mode, you cannot modify them. If you need to drop the constraint, you need to abort the add constraint operation using the following command:

ALTER INDEX ALL ON dbo.salesorderdetailtest ABORT;

The abort will stop the resumable status.

You can verify there are no more resumable operations with this query.

SELECT * FROM sys.index_resumable_operations

How to Pause and Resume Operations

Let’s say we want to pause the operation because we need to do some maintenance tasks in the database. To pause the add constraint operation in the database, we can use the following command:

ALTER INDEX ALL ON dbo.salesorderdetailtest PAUSE;

If we finish the maintenance task and want to resume the add constraint operation, we can use the ALTER INDEX with the RESUME option:

ALTER INDEX ALL ON dbo.salesorderdetailtest RESUME;

Conclusion

SQL Server 2022 has new options to handle and administer big tables. Some operations take a long time and require special options to pause and resume operations. In this tip, we learned how to work with this option, explained the arguments used, and helped solve some common problems.

Next Steps

To learn more about SQL Server 2022, refer to these links:

• New features in SQL Server 2022
• T-SQL enhancements in SQL Server 2022
• Get ready for SQL Server 2022