Resumable ADD CONSTRAINT in SQL Server 2022: Pausing a Primary Key Build

The maintenance window ends before the new primary key finishes building. Resumable ADD CONSTRAINT in SQL Server 2022 lets you preserve that work. The operation still needs online-build support, disk space, and a plan to finish.

Check What Resumable ADD CONSTRAINT Supports

The resumable extension applies to adding primary key and unique constraints. It doesn't make foreign key creation resumable. SQL Server 2022 introduced this support.

ONLINE must be ON, and the edition and operation must support the requested online build. Validate those prerequisites before proposing a production window. A supported syntax feature and a supported deployment are separate checks.

I check duplicate keys and nullability before scheduling the build. A primary key needs unique, non-null values. Resumability won't repair invalid source data.

Use a test copy for the example below. Its generated keys are deliberate sample input, not a measured large-table workload. The sample can finish before the duration expires, so don't assume it will enter a paused state.

Run Resumable ADD CONSTRAINT With a Time Budget

MAX_DURATION sets the running period before an unfinished resumable operation pauses. It is expressed in minutes. This is a maintenance budget, not a predicted completion time.

If the operation finishes earlier, the constraint is created normally. Use a duration suitable for the window and leave room for the operational checks around it. The command itself doesn't reserve resources.

The example creates a heap with a non-null key and adds a clustered primary key. Test the full table shape, including existing indexes, before applying that design elsewhere. A clustered key changes row organization and affects nonclustered locators.

Using resumable ADD CONSTRAINT changes how the build proceeds. It doesn't reduce the need to choose a suitable key or understand those storage effects.

CREATE TABLE dbo.ResumableConstraintDemo
(
    ItemId int NOT NULL,
    Description nvarchar(100) NOT NULL
);
INSERT dbo.ResumableConstraintDemo
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id),N'Sample'
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
ALTER TABLE dbo.ResumableConstraintDemo
ADD CONSTRAINT PK_ResumableConstraintDemo PRIMARY KEY CLUSTERED(ItemId)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 5);

With 10,000 rows, the build finished at once in my test, and the key appeared in sys.key_constraints. To rehearse a pause, I loaded a few million rows and ran the same ALTER TABLE again.

Pause an Active Build Deliberately

For resumable constraint creation, use ALTER INDEX ALL on the table to manage the operation. A manual PAUSE requires an operation that is still running. Use a second session when rehearsing that control.

Don't paste pause, resume, and abort into one block and run them together. They are alternative operational actions at different states, with different consequences.

I assign an owner to each paused operation. Leaving a build paused indefinitely retains state and consumes resources. The source and in-progress structures can both require storage and update work.

Pause means the build can continue later. It doesn't mean the unfinished structure disappeared. Keep the next window and its decision point in the maintenance record before walking away.

-- Run in a second session only while the resumable build is active.
ALTER INDEX ALL ON dbo.ResumableConstraintDemo PAUSE;

When the second session paused the build, the session running it received error 1219 and was disconnected. That is expected, so warn whoever owns that session. The progress view then showed PAUSED. With no build running, the same PAUSE fails with error 10680.

Read the Progress of Resumable ADD CONSTRAINT

The sys.index_resumable_operations view exposes the operation's state and progress. Scope the query to the intended table. Inspect state_desc, percent_complete, and page_count together with start information.

The reported percentage describes the engine's progress estimate. It doesn't guarantee a fixed completion time. Resource availability and concurrent writes change how much work remains when the next window arrives.

An empty result means no resumable operation is currently recorded for that object. Check whether the build completed before assuming a monitoring failure. Inspect sys.key_constraints and sys.indexes to confirm the final state.

The constraint name and table identity belong in every saved report. That prevents one maintenance session from resuming or aborting the wrong operation after a handoff.

SELECT object_id, index_id, name, state_desc, percent_complete, page_count
FROM sys.index_resumable_operations
WHERE object_id = OBJECT_ID(N'dbo.ResumableConstraintDemo');
SELECT name, type_desc
FROM sys.key_constraints
WHERE parent_object_id = OBJECT_ID(N'dbo.ResumableConstraintDemo');

Resume Under a New Window

RESUME continues retained work instead of starting the build from scratch. Provide a new duration budget if you need another bounded window. Review disk space, log behavior, and blocking before resuming.

The environment can change while the operation is paused. The fact that yesterday's window was safe doesn't certify that today's workload has the same headroom or competing activity.

Online operations still acquire locks at particular stages. Online doesn't mean every request is immune to blocking. Plan the start and finish transitions, including the team's response if they wait too long.

Keep the action narrow and observe the resulting state. The next command belongs only to a paused operation. A completed build doesn't need another resume request.

-- Run only when the operation is paused.
ALTER INDEX ALL ON dbo.ResumableConstraintDemo
RESUME WITH (MAX_DURATION = 5);

In my rehearsal, RESUME continued from the percentage the paused build had reached rather than from zero.

Abort When the Design No Longer Fits

ABORT discards the unfinished resumable operation. It is the explicit way to end retained build state when you aren't going to finish. Treat that as a different decision from pausing.

It gives up progress and requires a new build if the key is still needed. Record why the operation was abandoned and what happens to the intended constraint requirement afterward.

For example, a discovered key-design issue deserves review before more build time is spent. Don't resume merely because progress exists. Which constraint does the application actually require?

That answer outranks the sunk work. A half-built shelf has made progress, but it still isn't where you put the heavy boxes. Confirm completion before relying on the new key in application behavior.

-- Alternative to resuming, used when the unfinished build must be discarded.
ALTER INDEX ALL ON dbo.ResumableConstraintDemo ABORT;

After ABORT, the progress view returned no rows and sys.key_constraints showed no primary key on the table.

Confirm the Key After Completion

Check the final constraint and index definitions, then test the approved uniqueness behavior in a safe environment. Keep the operation reports with the build script. A maintenance run marked successful needs a completed key, not merely a command that returned after pausing. Make the finishing gate explicit in the job and handoff notes so incomplete work remains visible.

Use resumable ADD CONSTRAINT when a large supported key build needs several maintenance windows. Keep prerequisite validation, resource monitoring, and completion checks around it. Pausing protects useful progress.

It doesn't replace the responsibility to finish or explicitly abort. Operational ownership makes the feature dependable. The table keeps serving requests while the team manages its unfinished maintenance.

Keep duplicate detection in the preflight even if a previous test copy passed. New writes change the candidate key set. A resumable operation addresses interruption, while data constraints address correctness.

Treat them as complementary controls. The final verification needs to confirm both the intended definition and the completed state before the next dependent deployment starts.

Related reading on this blog: Resumable Index Rebuilds: Pausing Maintenance Without Losing Progress and Ins and Outs of Online Index Operations.

A paused constraint build is not a finished key, it is retained work with an owner.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.