Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Recent article regarding this problem: sqlservercentral.com/articles/Change+Tracking/74397 –  8kb Aug 11 '11 at 14:03

3 Answers 3

up vote 18 down vote accepted

"It depends"

If you add a column that does require adding data to the rows, 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?

share|improve this answer
2  
+1 on the backup. and make sure you have enough log space too. –  SqlACID Aug 9 '11 at 20:28
    
Can you clarify why adding an int or char requires physical row movements? –  sh-beta Aug 10 '11 at 17:41
1  
Did you mean "doesn't" require adding data to the rows in your second line? –  Ben Brocka Mar 1 '12 at 14:42
    
@HLGEM that was from way back in August, and was an aside. I honestly forget now for what. probably for breaking 20k rep. –  jcolebrand Mar 1 '12 at 15: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.

share|improve this answer
    
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 '12 at 14:23
    
If anyone knows a way around this, I love to know... In contrast, in Oracle, changing null to not null does a lock, then a select to verify no non nulls, then an instantaneous purely meta data update. –  user7116 Mar 1 '12 at 14:23
    
Hey @Mike, this sounds like a good potential question in its own right. –  Derek Downey Mar 1 '12 at 15:18

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.

share|improve this answer
    
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. –  Jon of All Trades Oct 4 '11 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 '11 at 13:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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