patternsqlMajor
Adding columns to production tables
Viewed 0 times
tablescolumnsaddingproduction
Problem
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?
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?
Solution
"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 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?
Context
StackExchange Database Administrators Q#4506, answer score: 28
Revisions (0)
No revisions yet.