Return to Revisions

While commendable, your concern regarding adding and dropping columns is mostly misplaced.

Adding a nullable column is a metadata-only operation: that is, it only entails minor changes to the database's definition of the table in internal tables, and does not require rewriting the clustered and all the non-clustered indexes. This means that such a modification is very fast. Likewise, dropping a column is also metadata-only, as the storage engine will simply ignore that data until each page is eventually overwritten without it.

You can see this in action in this fiddle. SET STATISTICS IO ON is used to show reads of the table itself, and you can see that it only happens on the addition of a non-nullable column.

Your only real concerns should be:

• Possible blocking chains.
  For example, a long-running SELECT holds a Sch-S lock on the table. The ALTER tries to take a Sch-M lock and waits. All other SELECT and modification queries then pile up behind it, waiting on their own Sch-S lock which they can' take.
  Unfortunately, this can't be avoided using WAIT_AT_LOW_PRIORITY as that's not yet implemented for this type of ALTER. Your best bet is to put the following before your ALTER:

  SET LOCK_TIMEOUT 10000;  -- milliseconds
  

• All views and procedures which access this table will need their metadata refreshed. You can use the following script, which gets all such objects and refreshes them:

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(N'
EXEC sp_refreshsqlmodule N''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ''';',
      '')
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE o.object_id IN (
        SELECT ed.referencing_id
        FROM sys.sql_expression_dependencies ed
        WHERE ed.referenced_id = OBJECT_ID(@yourTable)
    )
      AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
);

PRINT @sql;

EXEC sp_executesql @sql;