Adding a nullable column concurrently with an update or insert

In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update?

For example:

ALTER TABLE MyTable ADD MyColumn varchar(255) NULL;

Referencing the documentation for ALTER TABLE (highlighting mine),


The changes specified in ALTER TABLE are implemented immediately. If
the changes require modifications of the rows in the table, ALTER
TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M)
lock on the table to make sure that no other connections reference
even the metadata for the table during the change, except online index
operations that require a very short SCH-M lock at the end. In an
ALTER TABLE…SWITCH operation, the lock is acquired on both the source
and target tables. The modifications made to the table are logged and
fully recoverable. Changes that affect all the rows in very large
tables, such as dropping a column or, on some editions of SQL Server,
adding a NOT NULL column with a default value, can take a long time to
complete and generate many log records. These ALTER TABLE statements
should be executed with the same care as any INSERT, UPDATE, or DELETE
statement that affects many rows.

Also, Paul Randal has some good information about what happens when you add columns (and how long the operation might take) in Misconceptions around adding columns to a table. To summarize some main points:

• New column is nullable, with a NULL default. The table’s metadata

records the fact that the new column exists but may not be in the
record. This is why the null bitmap also has a count of the number of
columns in that particular record. SQL Server can work out whether a
column is present in the record or not. So – this is NOT a
size-of-data operation – the existing table records are not updated
when the new column is added. The records will be updated only when
they are updated for some other operation.

-
New column is nullable, with a non-NULL default. It depends which
version of SQL Server you’re using:

-
Before SQL Server 2012: This IS a size-of-data operation. The
non-NULL default forces all existing records to be updated when the
column is added, and so the null bitmap will be updated too.

-
SQL Server 2012 onward: same behavior as for a NULL default nullable
column (i.e. metadata only operation)

• New column is not-nullable (obviously with a non-NULL default). This

IS a size-of-data operation.

StackExchange Database Administrators Q#170789, answer score: 5