Size-of-data operations when adding and removing columns

Question: I’m a little confused by what happens when a column is added or removed from a table. It seems that for some columns it takes a long to time to add the column, while for others it takes no time. Also, dropping a column is always fast, no matter what kind of column is being dropped. Can you explain what I’m seeing please?

Answer: I understand that the behavior can be a little confusing, but it is by-design and intuitive, once you know what’s happening under the covers.

There are two main sets of column metadata maintained by SQL Server – one for the relational definition of the table, and one for how the columns are stored in the records themselves. You could think of them as the Query Processor’s view of the table, and the Storage Engine’s view of the table.

When you add a column to a table, both sets of column metadata are updated to include the new column. However, the table records themselves may not have to be updated as part of adding the new column.

If the new column has a non-null default value, then that value must be entered into the table records at that time—this is a size-of-data operation (i.e. each record in the table must be updated). The following code would result in a size-of-data operation:

ALTER TABLE PaulsTable ADD QuantityColumn INT DEFAULT 0;

If the column has a null default value, then there is no need to update each record. As well as the Storage Engine column metadata, each record contains a count of the number of columns present in the record. The Storage Engine can tell whether a record does not yet contain the newly added column and can return a NULL if the column is selected from such a record. The record will not be expanded to include the space for the newly added column until the record is first updated.

There is also a myth that adding a default null column to a record will cause the record to expand if the column is the 9th, 17th, 25th, etc column in the record (I.e. one that would cause the null bitmap in the record to have to expand to store the null bit for the column). This is not the case, as the Storage Engine metadata accounts for this too, and the null bitmap will be expanded the first time the record is updated.

When a column is dropped from a table, there is no need for a size-of-data operation immediately as the column is simply marked as a dropped column in the Storage Engine metadata. It will continue to take up space in the records in which it is present until the record is next updated (either by regular DML operations or something like an index rebuild). If the column is a variable-length column you may want to force the space to be reclaimed by rebuilding the index or using the DBCC CLEANTABLE command.

This should explain why sometimes you see a size-of-data operation and sometimes you don’t.