31

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?

1

4 Answers 4

28

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

0
21

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.

1
  • Re the last bit: •add the not null/default constraints I'm not sure there isn't a potential problem with this... When MSSQL (even 2008R2) changes a not null column to null, if you put a trace on you can see it actually under the covers doing a full update of every row of the table, i.e. update table1 set column1 = column1 I assume it's doing the not-null verification in a completely idiotic way. This transaction is twice the size of the table (before and after pages) so for a DW table can be huge. Previously we've had to bcp data out, truncate, do null to non-null change, then bcp in.
    – user7116
    Mar 1, 2012 at 14:23
4

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.

2
  • Wouldn't you need a write lock, rather than read? It's fine for users to see data in the old table, you just don't want them to commit any changes which would be overwritten when you finish the buffer swap. Oct 4, 2011 at 21:05
  • That was my thinking with my data warehouse hat on where changes can be controlled a little easier. In an OLTP situation you're correct, a write lock would be necessary to avoid changes being made to the table.
    – RobPaller
    Oct 5, 2011 at 13:48
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.