Adding columns to production tables

"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?

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.

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.