Online ALTER TABLE ADD NOT NULL with DEFAULT vs Full Table Rebuild: Unresolved Blocking Duration in SQL Server 2019+

Question

Comparing Online ADD NOT NULL with DEFAULT versus Full Table Rebuild
When a small application requires a new column that cannot be null and must be initialized with a constant, two documented paths exist in SQL Server 2019 and later: an online ALTER TABLE that attempts to add the column without rebuilding the entire table, and a traditional rebuild that locks the table for the duration of the operation.
The online variant claims to allow concurrent reads and writes, yet it still acquires brief metadata locks that can block DML for milliseconds. The exact length of this blocking window is not documented, and early tests suggest it may depend on table size, active connections, and cumulative update level.
Because the application’s uptime is critical, the unresolved question is whether the online approach truly eliminates noticeable downtime for a table that contains millions of rows, or if the brief blocking period is sufficient to impact latency‑sensitive workloads.
What is the documented maximum duration of the blocking period for an online ADD NOT NULL with DEFAULT? Does this duration vary predictably with table size or current transaction load? Are there specific cumulative updates that change the behavior of this online DDL operation?

Tasadduq Burney · Answer

Answer
The documented maximum duration of the blocking period for an online ALTER TABLE … ADD NOT NULL WITH DEFAULT is not published; Microsoft describes the final schema‑modification (Sch‑M) lock as “brief” and states it does not scale with row count.
Confirmed facts

The operation is metadata‑only: the column is added as nullable with a default, then a short Sch‑M lock is taken to enforce NOT NULL.
Observed block times are usually sub‑second (often < 100 ms) under light workloads.
The length of the Sch‑M lock depends on how long existing transactions hold locks on the table, not on table size, index count, or data volume.
No cumulative update changes the fundamental online nature; however, CUs 2‑5 of SQL Server 2019 contain fixes that reduce the chance of a prolonged Sch‑M lock under heavy concurrent DML.

Likely explanation
Because the lock must wait for any in‑flight transactions that reference the table to drain, the blocking window can extend if a long‑running transaction holds a lock (e.g., an open transaction or a long‑running batch). Under typical OLTP load the drain is quick, giving the impression of negligible downtime.
Steps to verify in your environment

Create an Extended Events session that captures lock_acquired and lock_released for LCK_M_SCH_M on the target database.
Run the ALTER TABLE … ADD NOT NULL WITH DEFAULT statement while generating a realistic DML load.
Measure the elapsed time between the acquire and release events; the maximum observed wait_time from sys.dm_exec_requests where wait_type = LCK_M_SCH_M gives the block duration.
Repeat after applying the latest cumulative update to see if the observed maximum changes.

Missing diagnostic detail
To decide whether the online approach is safe for your latency‑sensitive workload, we need to know whether any long‑running transactions regularly hold locks on the table. If such transactions exist, the Sch‑M lock could be blocked for their remaining duration, potentially causing noticeable latency.

Online ALTER TABLE ADD NOT NULL with DEFAULT vs Full Table Rebuild: Unresolved Blocking Duration in SQL Server 2019+

Comparing Online ADD NOT NULL with DEFAULT versus Full Table Rebuild

1 answer

Answer

Confirmed facts

Likely explanation

Steps to verify in your environment

Missing diagnostic detail

Your perspective belongs here.

0 question comments