Adding columns to production tables

Question

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:

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

(http://msdn.microsoft.com/en-us/library/ms190273.aspx)

On a large table with millions of rows, this can take a while. Is taking an outage the only option? What's the best way to handle this kind of situation?

Answer 1

"It depends"

If you add a column that does not require adding data to the rows, then it can be quite quick.

For example, adding an int or char requires physical row movements. Adding a nullable varchar with no default shouldn't (unless the NULL bitmap needs to expand)

You need to try it on a restored copy of production to get an estimate

Creating a new table, copying, renaming may take longer if you have to re-add indexes and keys on a billion row table.

I have changed billion row tables that took a few second to add a nullable column.

Did I say to take a backup first?

Answer 2

If the column is NULLable, the impact should be negligible. If the column cannot be NULL and the value must be set, then it can be quite different. What I would do in this case is, instead of adding a not null and default constraint in one shot, effectively adding data to every row:

• add the column as NULLable - should be quick in most cases
• update the values to the default
  • you can do this in batches if necessary
  • you can also use this to apply conditional logic where some rows might not get the default
• add the not null / default constraints
  • this will be faster when none of the data is NULL, but should still be measurable

Agree with @gbn that you can test this by restoring a copy of production and trying it there... you'll get a good idea of timing (assuming hardware is somewhat similar) and you can also see the impact on the transaction log.

Answer 3

Have you considered:

1. Creating a new table that includes the changes to the table definition.
2. Inserting into the new table definition selecting from the original table.
3. Renaming the original table to _orig and then renaming the new table to the original table name.

The disadvantage here is that you have to have sufficient space in the database to make this change. You may still require a read lock on the table to prevent any dirty reads.

However, you minimize the impact to the end users if there is a chance or need for the original table to be accessed concurrently. It should also minimize the lock durations.

Answer 4

I have a special exception that I feel should be mentioned.

With SQL Server 2012 Enterprise and newer, adding a new NOT NULL column with a runtime constant is an online operation that is completed instantly and does not depend on the number of rows in the table.

More information about this is found in MSDN

I'll reproduce the important section

Starting with SQL Server 2012 (11.x) 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 despite the number of rows in the table. Because, the existing rows in the table aren't 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.