Misconceptions around adding columns to a table

This is a quick follow-on from my Misconceptions around null bitmap size post.

The null bitmap is *always* present in a data record (i.e. records in a heap or the leaf-level of a clustered index) except when all columns are defined as SPARSE in SQL Server 2008 onwards, but is optional in index records if all the columns in the index records are not nullable. The misconception is around what happens when a new column is added to the table. The common misconception is that if you have 8 columns in the table (and hence 8 bits in the null bitmap), if you add a ninth column then SQL Server has to go update every record so the null bitmaps all contain 9 bits. (Same misconception applies to adding the 17th, 25th, 33rd, etc column).

This is usually not true. Let’s consider the cases:

  • 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.

Hope this helps.

2 thoughts on “Misconceptions around adding columns to a table

  1. I know this is an old post, but just wanted to clarify for future searchers and make sure I have things correct. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql states “Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows.”

    So.. adding a new non-NULL column in SQL 2012 Enterprise with a default may or may not be a size-of-data operation. In other words, it depends :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.