Addding a new column to table in use...
-
Yes, you can add a column to a table in production. If it does not allow nulls, you need to include a default value. Also, you should add it to the "end" of the list of columns for the table, else you will have to run a change script that creates a new temporary table with the new column, copies the existing data from the original table to the new table, drops (or renames) the original table, then renames the temporary table to the original table name.
-
Look up the statement ALTER TABLE and then follow Lynn Pettis' advice above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
We've used the Alter Table statement on a fairly busy database a few times to add a column, and have been very pleased with the utter lack of a noticable performance hit.
Student of SQL and Golf, Master of Neither -
This feature is not new to SQL 2005 - it can be done in SQL 2000 and prehaps prior versions. My guess is that SQL 7 supports it as well.
- Paul
http://paulpaivasql.blogspot.com/
-
It's possible that a transaction would block the DDL and, therefore, create blocking. This could be a nail biter if the table is really "hot" such as an "Order" table in an OLTP environment.
CQLBoy
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply