SQL Server 2022 Pause and Resume Creation of Table Constraints

By:   |   Updated: 2023-04-07   |   Comments   |   Related: > SQL Server 2022


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.


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.

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
  •  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

The error message is the following:

Msg 1505, Level 16, State 1, Line 6
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.salesorderdetailtest' and the index name 'PK_salesord3'. The duplicate key value is (43659).

Msg 1750, Level 16, State 1, Line 6
Could not create constraint or index. See previous errors.

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
Status of the table

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

Here is the error.

Msg 10637, Level 16, State 1, Line 19
Cannot perform this operation on 'object' with ID 1959678029 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

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
Checking for 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;


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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-04-07

Comments For This Article

get free sql tips
agree to terms