The change request adds one flag to a very large table. A NOT NULL column with a default can be cheap or expensive, depending on its definition and edition. Check the operation before booking the maintenance window.
Check the Edition and Definition First
I check the edition before accepting a tiny estimated change window. The same ALTER TABLE statement can perform different amounts of physical work. A constant flag and a generated identifier aren't equivalent additions.
Eligible Enterprise edition tables support a metadata-based addition for a NOT NULL column with a runtime constant default. Developer editions with that feature support provide a useful test environment. Match the target edition's capabilities when rehearsing.
Existing rows can obtain the value from metadata instead of an immediate row-by-row update. Later changes materialize it as needed. That is the reason the initial change avoids rewriting every existing row.
The optimization isn't a blanket promise for every type and default. SQL Server still needs schema locks. Metadata work can wait behind a long transaction.
SELECT SERVERPROPERTY('Edition') AS EditionName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
SELECT name, compatibility_level FROM sys.databases WHERE database_id = DB_ID();Test a NOT NULL Column With a Default on a Copy
Use a disposable table before trying a production change. The generated rows are sample data, not an observed capacity figure. Choose a representative restored copy for the real rehearsal.
The default below is a constant zero. Existing rows must return that value after the addition. New inserts also receive it when they omit the column.
Read allocation before and after with sys.dm_db_partition_stats. An unchanged allocation supports the expected behavior in that test. It isn't a universal guarantee about future row modifications.
Capture duration and blocking on your own server. Don't infer production lock behavior from an idle small sample. The schema lock still has to meet real concurrent readers.
CREATE TABLE dbo.AddFlagDemo
(
ItemId int NOT NULL PRIMARY KEY,
DescriptionText varchar(100) NOT NULL
);
INSERT dbo.AddFlagDemo(ItemId, DescriptionText)
SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY object_id), 'Sample row'
FROM sys.all_objects;
SELECT index_id, row_count, reserved_page_count, used_page_count
FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.AddFlagDemo');
ALTER TABLE dbo.AddFlagDemo
ADD IsReviewed bit NOT NULL CONSTRAINT DF_AddFlagDemo_IsReviewed DEFAULT (0);
SELECT index_id, row_count, reserved_page_count, used_page_count
FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.AddFlagDemo');
SELECT TOP (10) ItemId, IsReviewed FROM dbo.AddFlagDemo ORDER BY ItemId;Recognize When a NOT NULL Column With a Default Costs More
NEWID generates a distinct value for each row. It cannot give every existing row one shared runtime constant. Adding that default requires work across the data.
Large-value and specialized types also have restrictions on the optimized operation. Examples include varchar(max), nvarchar(max), varbinary(max) and XML. Check the documented ALTER TABLE restrictions for the exact type.
The maximum row size and table features deserve review too. A fixed-width addition changes the eventual row layout. A metadata-based start doesn't erase that physical requirement.
This next statement deliberately demonstrates an expensive category on the disposable sample. Don't apply it to a large production table as a casual comparison. Its work and log requirements belong in the rehearsal. The GO line lets the final SELECT compile after the new column exists.
ALTER TABLE dbo.AddFlagDemo
ADD RowToken uniqueidentifier NOT NULL
CONSTRAINT DF_AddFlagDemo_RowToken DEFAULT NEWID();
GO
SELECT TOP (10) ItemId, RowToken FROM dbo.AddFlagDemo ORDER BY ItemId;
Stage a NOT NULL Column With a Default on Standard Edition
A Standard edition plan should account for a size-of-data operation when the direct addition doesn't support the optimization. A staged nullable column avoids forcing the initial backfill into one large change. It also needs an application transition plan.
Add the nullable column without populating existing rows. Add a default for future inserts. That default doesn't fill the old NULL values by itself.
The application must tolerate NULL during the transition. It must also avoid writing NULL into the new column. Deploy that rule before assuming the backfill will remain complete.
A NOT NULL column with a default is the final state. Several deployment steps are allowed to reach it. Record the intermediate states so support staff understand them.
CREATE TABLE dbo.AddFlagStandardDemo
(
ItemId int NOT NULL PRIMARY KEY,
DescriptionText varchar(100) NOT NULL
);
INSERT dbo.AddFlagStandardDemo VALUES(1, 'First sample'), (2, 'Second sample');
ALTER TABLE dbo.AddFlagStandardDemo ADD IsReviewed bit NULL;
ALTER TABLE dbo.AddFlagStandardDemo
ADD CONSTRAINT DF_AddFlagStandardDemo_IsReviewed DEFAULT (0) FOR IsReviewed;Backfill With Small Committed Batches
The following loop updates rows still missing the flag, and it left zero NULL rows in my test. Each statement uses its own autocommit transaction. Don't wrap the entire loop inside an outer transaction.
Choose the batch size from a representative test. Watch transaction log usage and blocking while it runs. A brief pause gives other work a chance between statements.
For a large table, use an indexed key-range approach appropriate to its distribution. Repeatedly searching all completed rows is wasteful. The sample loop favors clarity over a universal production backfill plan.
DECLARE @Changed int = 1;
WHILE @Changed > 0
BEGIN
UPDATE TOP (1000) dbo.AddFlagStandardDemo
SET IsReviewed = 0 WHERE IsReviewed IS NULL;
SET @Changed = @@ROWCOUNT;
IF @Changed > 0 WAITFOR DELAY '00:00:00.100';
END;
SELECT COUNT_BIG(*) AS RemainingNulls
FROM dbo.AddFlagStandardDemo WHERE IsReviewed IS NULL;Finish With a Protected Validation Window
Changing NULL to NOT NULL requires validation and locking. It isn't automatically a tiny metadata change. Test its duration and schedule it with the necessary write coordination.
Stop writers that can still introduce NULL before the final check. Validate the application deployment and remaining rows. Then apply the final alteration during the planned window.
I don't present batching as the removal of all blocking. It spreads backfill work across smaller transactions. The final schema change still deserves a separate plan.
IF EXISTS(SELECT 1 FROM dbo.AddFlagStandardDemo WHERE IsReviewed IS NULL)
THROW 51000, 'Backfill is incomplete.', 1;
ALTER TABLE dbo.AddFlagStandardDemo ALTER COLUMN IsReviewed bit NOT NULL;Keep the Maintenance Promise Realistic
How long can the application wait for the schema lock? Answer that alongside the data-size question. A blocked metadata operation can still exceed the permitted outage.
Use a bounded lock wait policy during the rehearsal. Define how the deployment aborts and retries if it cannot acquire the lock. Don't leave an unattended schema request blocking unrelated work indefinitely.
I review the default expression again before signing off. A late change from zero to per-row generation changes the operational plan. One word in a default can move a lot of data.
A NOT NULL column with a default needs an edition-aware rehearsal. Preserve the exact type, expression and table features in that rehearsal. Then schedule the measured operation you have tested.
Review transaction log headroom before either the direct operation or backfill. Under full recovery, keep the scheduled log backups running. Repeated committed batches don't automatically make all log records reusable.
Also rehearse cancellation and restart of the deployment. The nullable stage should remain understandable if the backfill stops. The remaining-NULL query gives the next operator a concrete restart point.
A default applies when an insert omits the column. Explicit NULL remains permitted during the nullable stage. Coordinate that application behavior rather than relying on the default to reject it.
After completion, inspect the column nullability and default definition through system catalogs. Verify a new insert that omits the flag. The migration is finished when both old rows and new writes meet the final rule.
Related reading on this blog: Adding Default Value to Existing Table and How to Change Column Property From NULL to NOT NULL Value?.
A column addition is not always a row rewrite, it is an operation defined by its type and default.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
