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

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?