Addding a new column to table in use...
-
Is it possible to add a new column to a sql 2005 table while it is still running in production. User can't be stopped from using the database( means you cannot tell people to get off it for few seconds while you update the table). My interviewer said, it is possible and that there is way to add a new column to the table in production database while it is still being used. Is there any ?
thanks,
kushpaw
-
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply