One new column, more migration than you think

The dangerous version is one statement

The requirement sounds harmless: add a required status column to a large table and populate the existing rows. The tempting migration adds the column as NOT NULL with a default, updates everything, and perhaps creates an index—all inside the release transaction.

On SQL Server, ALTER TABLE requires a schema modification lock. If the change touches every row, it can also generate substantial log activity and hold the release open while normal traffic waits. Even metadata-only changes still need a moment when no conflicting schema references block the lock.

The safer goal is not “no locks.” It is a short, observable schema change followed by resumable data work outside the deployment’s critical path.

Step zero: learn the table

Before writing DDL, measure row count, table size, write rate, longest transactions, replication or temporal-table behavior, and available log space. Check the SQL Server version and edition before assuming an online or resumable option exists.

Search application code, views, procedures, bulk imports, and SELECT-star queries for assumptions about the current shape. The migration plan begins with every consumer that must tolerate both schemas during the rollout.

SELECT
    SUM(p.rows) AS row_count,
    SUM(a.total_pages) * 8 / 1024 AS size_mb
FROM sys.partitions AS p
JOIN sys.allocation_units AS a
  ON p.partition_id = a.container_id
WHERE p.object_id = OBJECT_ID('dbo.Records')
  AND p.index_id IN (0, 1);

Step one: expand with a nullable column

Add the column as nullable and do not populate historical rows in the same statement. This keeps the schema operation small. Set a short lock timeout so the release fails instead of waiting indefinitely behind a long transaction; retry the DDL during a quieter moment if necessary.

The ALTER TABLE still requests a schema modification lock. Keeping the operation metadata-focused reduces the time it needs the lock, but deployment monitoring should still watch blocking and latency.

SET LOCK_TIMEOUT 5000;

IF COL_LENGTH('dbo.Records', 'ProcessingState') IS NULL
BEGIN
    ALTER TABLE dbo.Records
        ADD ProcessingState tinyint NULL;
END;

Step two: release compatible code

Deploy application code that writes the new column for new or changed records while still tolerating NULL for historical data. Reads can temporarily interpret NULL as the legacy behavior. This is the expand phase: old and new states are both valid.

Do not make the new column mandatory in the application until every writer is updated. Background workers, administrative scripts, integrations, and older deployment instances are easy to miss during a rolling release.

SELECT
    RecordId,
    COALESCE(ProcessingState, 0) AS ProcessingState
FROM dbo.Records
WHERE RecordId = @RecordId;

Step three: backfill in committed batches

Backfill outside the release transaction. Select a small deterministic batch, update it, commit, record progress, and repeat. Short transactions limit log growth and reduce the time rows remain locked. The batch size should be tuned from production measurements rather than copied from an example.

The query below performs one batch. A job runner should call it repeatedly with pacing, metrics, cancellation, and retry behavior. Lock hints are requests to the optimizer, not a promise that escalation can never occur, so watch the database while the backfill runs.

;WITH next_batch AS (
    SELECT TOP (1000) RecordId
    FROM dbo.Records WITH (READPAST, UPDLOCK, ROWLOCK)
    WHERE ProcessingState IS NULL
    ORDER BY RecordId
)
UPDATE records
SET ProcessingState = 0
FROM dbo.Records AS records
JOIN next_batch
  ON next_batch.RecordId = records.RecordId;

SELECT @@ROWCOUNT AS rows_updated;

Step four: validate before tightening

Track the remaining NULL count and verify that every writer supplies a valid value. Add a default constraint for future inserts only after its meaning is deliberate; a default can hide a missing application decision just as easily as it can enforce a useful baseline.

Changing the column to NOT NULL is a separate operational event. SQL Server may need to verify the table while holding a schema modification lock. Schedule it based on measured behavior, or decide that a nullable storage column with enforced application semantics is the better tradeoff for the current system.

SELECT COUNT_BIG(*) AS remaining_rows
FROM dbo.Records
WHERE ProcessingState IS NULL;

ALTER TABLE dbo.Records
ADD CONSTRAINT DF_Records_ProcessingState
DEFAULT (0) FOR ProcessingState;

Step five: treat the index as its own release

If the new access pattern needs an index, do not casually append it to the schema migration. Online index operations still take short shared or schema modification locks at their boundaries, require additional space, and are not supported in every SQL Server edition or for every index shape.

On supported versions and editions, online and resumable creation lets the operation pause and continue. Low-priority lock waiting can keep ordinary traffic ahead of the index request. Confirm support in the exact environment and choose whether the index should stop itself or eventually interrupt blockers.

CREATE INDEX IX_Records_ProcessingState
ON dbo.Records (ProcessingState, RecordId)
WITH (
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 2 MINUTES,
            ABORT_AFTER_WAIT = SELF
        )
    ),
    RESUMABLE = ON,
    MAX_DURATION = 30 MINUTES
);

Only contract when rollback is boring

After the backfill, validation, and observation window, remove the temporary NULL fallback from application code. Retire compatibility paths separately from adding the schema. Dropping the old behavior should be the last step, not the same release that introduces the new column.

A mature migration is a sequence of reversible decisions. The SQL may be simple; the operational choreography is the real work.