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