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 in 2005 Microsoft implemented a real ANSI SQL ALTER Table ADD Column, in 2000 you can only use Enterprise Manager for it but now you can do it with code. Try the link below for details.
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Kind regards,
Gift Peddie -
Why couldn't you just use the alter table statement in 2000? I never add any problems doing that in production (or maybe I was just lucky)
, -
The alter table statement adds the column to the "end" of the record. I was also alluding to the use of EM/SSMS to modify the table. I have had developers insist that new columns be next to specific existing columns, even though it really does not matter in a relational database what order the columns are in.
-
That explains it... I never had any problems with the columns order, but then again I never (except for Exists) use the Select * combinaison
. -
Lynn has covered it ALTER ADD adds the column at the end of the table, if you want the new column in a specific location you use Enterprise Manager which drops the original table and recreates a new one with your new column something I would not do in production with a large table.
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply