Add a defaulted SQL Server column with a safe plan for a busy table

You need to add IsArchived to a table with hundreds of millions of orders. The syntax is short; the operational question is whether SQL Server can make it a metadata change or must touch existing rows while holding locks and generating log. Treat the default, backfill and nullability as separate contracts.

Define what existing and future rows should mean

Population	Required value	Mechanism
Existing orders	0	Immediate or phased backfill
New inserts omitting column	0	Default constraint
New inserts explicitly sending NULL	Reject	NOT NULL
Application reads during rollout	Stable interpretation	Compatibility release

A default is applied when an insert omits the column. It does not override an explicit NULL on a nullable column. WITH VALUES controls population of existing rows when adding a nullable column with a default.

Inventory the target before changing it

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.partitions AS p ON p.object_id = t.object_id
WHERE s.name = N'dbo'
  AND t.name = N'Orders'
  AND p.index_id IN (0, 1)
GROUP BY s.name, t.name;

SELECT
    c.name,
    TYPE_NAME(c.user_type_id) AS data_type,
    c.max_length,
    c.is_nullable,
    dc.name AS default_constraint
FROM sys.columns AS c
LEFT JOIN sys.default_constraints AS dc
  ON dc.parent_object_id = c.object_id
 AND dc.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID(N'dbo.Orders');

Record SQL Server version, edition, compatibility level, table size, partitioning, replication/change-capture features, temporal configuration, long-running readers, log free space, recovery model and deployment timeout. Test on a production-shaped copy, not an empty developer table.

Use the direct statement when its lock profile is acceptable

ALTER TABLE dbo.Orders
ADD IsArchived bit NOT NULL
    CONSTRAINT DF_Orders_IsArchived DEFAULT (0);

Microsoft documents that a non-null column added to a nonempty table needs a default and that existing rows receive it. On supported SQL Server configurations, a runtime-constant default may be eligible for a metadata-only operation; expressions that are not runtime constants can require physical row changes and a schema-modification lock for the duration. Eligibility is not permission to skip a rehearsal.

Name the default constraint

CONSTRAINT DF_Orders_IsArchived DEFAULT (0)

Without an explicit name, SQL Server generates one that can differ across environments. A stable name makes future migration scripts deterministic:

ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_IsArchived;

The default constraint is a database object; it is not embedded application logic. Keep its name and intended value in schema source control.

Use WITH VALUES only for the semantics it provides

ALTER TABLE dbo.Orders
ADD ReviewState tinyint NULL
    CONSTRAINT DF_Orders_ReviewState DEFAULT (0)
    WITH VALUES;

For a nullable new column, WITH VALUES stores the default for existing rows. Without it, existing rows remain NULL while later inserts that omit the column get zero. For a non-null new column on a nonempty table, the required default already supplies existing values.

Choose a phased migration when touching every row is risky

ALTER TABLE dbo.Orders
ADD IsArchived bit NULL;

ALTER TABLE dbo.Orders
ADD CONSTRAINT DF_Orders_IsArchived
DEFAULT (0) FOR IsArchived;

Deploy application code that writes the value explicitly and reads NULL as the temporary legacy meaning. The default protects older insert paths that omit the column. This expand phase avoids immediately requiring a table-wide physical update, but it creates a temporary three-state schema that must be documented and completed.

Backfill in bounded, restartable batches

SET XACT_ABORT ON;

WHILE 1 = 1
BEGIN
    UPDATE TOP (5000) dbo.Orders
       SET IsArchived = 0
     WHERE IsArchived IS NULL;

    IF @@ROWCOUNT = 0 BREAK;

    WAITFOR DELAY '00:00:00.200';
END;

Batch size and delay must be tuned from measurements. A scan for every batch can become progressively expensive; use an indexed key-range cursor on very large tables:

DECLARE @LastId bigint = 0;

WHILE 1 = 1
BEGIN
    UPDATE o
       SET IsArchived = 0
      FROM dbo.Orders AS o
     WHERE o.OrderId > @LastId
       AND o.OrderId <= @LastId + 50000
       AND o.IsArchived IS NULL;

    IF @LastId >= (SELECT MAX(OrderId) FROM dbo.Orders) BREAK;
    SET @LastId += 50000;
    WAITFOR DELAY '00:00:00.200';
END;

Do not assume IDs are dense or immutable; this range is a progress boundary, not a row-count guarantee. Persist the cursor outside the session if the operation must survive restarts.

Monitor locks, blocking and transaction-log pressure

SELECT
    r.session_id, r.status, r.command,
    r.blocking_session_id, r.wait_type,
    r.wait_time, r.percent_complete
FROM sys.dm_exec_requests AS r
WHERE r.session_id <> @@SPID;

SELECT
    total_log_size_mb,
    active_log_size_mb,
    log_truncation_holdup_reason
FROM sys.dm_db_log_stats(DB_ID());

Use the monitoring views available to your supported version and permissions. Watch replica lag, CDC/replication backlog, storage latency and application timeouts. A schema change waiting for Sch-M can block behind a long reader while newer requests queue behind it; set a deployment lock timeout and abort rather than causing an unbounded outage.

SET LOCK_TIMEOUT 10000;
ALTER TABLE dbo.Orders
ADD IsArchived bit NOT NULL
    CONSTRAINT DF_Orders_IsArchived DEFAULT (0);
SET LOCK_TIMEOUT -1;

Validate before enforcing NOT NULL

SELECT COUNT_BIG(*) AS remaining_nulls
FROM dbo.Orders
WHERE IsArchived IS NULL;

SELECT IsArchived, COUNT_BIG(*) AS rows_per_value
FROM dbo.Orders
GROUP BY IsArchived;

Stop old application versions from writing nulls before the final check. A clean count can become stale immediately if an unpatched writer is still active.

Contract the schema after every writer is compatible

ALTER TABLE dbo.Orders
ALTER COLUMN IsArchived bit NOT NULL;

This final alteration may scan or lock the table depending on version and conditions. Rehearse it separately. Keep the default constraint so future inserts that omit the column receive zero; removing nullability does not create a default.

Handle date defaults with the correct time semantics

ALTER TABLE dbo.Orders
ADD CreatedAt datetime2(3) NOT NULL
    CONSTRAINT DF_Orders_CreatedAt
    DEFAULT (SYSUTCDATETIME());

A function such as SYSUTCDATETIME() is evaluated for inserts, but adding it to an existing large table can have a different physical cost from a constant default. Decide whether every historical row should receive migration time, original business time reconstructed by a backfill, or remain temporarily null. Do not silently invent historical timestamps.

Test application compatibility before rollout

• ORM models that enumerate columns or map inserts positionally.
• SELECT * consumers expecting a fixed shape.
• Bulk-copy jobs and ETL packages with explicit mappings.
• Replication, CDC, temporal tables and audit triggers.
• Old binaries that explicitly insert NULL.
• Downstream schemas and data contracts.

BEGIN TRANSACTION;
INSERT dbo.Orders (CustomerId, TotalAmount)
VALUES (42, 125.00);

SELECT TOP (1) OrderId, IsArchived
FROM dbo.Orders
ORDER BY OrderId DESC;
ROLLBACK;

Prepare rollback according to the phase

Before application code depends on the column, rollback can drop the constraint and column after verifying no data must be retained. After writers use it, dropping the column destroys state and may break deployed binaries. At that point rollback usually means rolling forward with a compatibility fix while leaving the expanded schema in place.

ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_IsArchived;

ALTER TABLE dbo.Orders
DROP COLUMN IsArchived;

Run destructive rollback only inside the approved window and after confirming dependencies. Back up schema and required data according to policy; a transaction around a long schema operation is not a substitute for a tested recovery plan.

Close with evidence

SELECT
    c.name, c.is_nullable,
    dc.name AS default_name,
    dc.definition AS default_definition
FROM sys.columns AS c
LEFT JOIN sys.default_constraints AS dc
  ON dc.parent_object_id = c.object_id
 AND dc.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID(N'dbo.Orders')
  AND c.name = N'IsArchived';

Record duration, peak blocking, log growth, backfill rows, null count, constraint definition, application error rate and replica health. That evidence proves both schema state and operational impact.

The default controls omitted values on future inserts. Existing-row population and NOT NULL enforcement are separate migration decisions whose cost must be measured on the real table shape.

Sources: the solved Stack Overflow question (question by Mathias; accepted answer by James Boother, CC BY-SA), and Microsoft’s official ALTER TABLE and default-value documentation.