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 lock
microsoftsqlserver