May 26, 2009 at 10:33 am
Would adding a new field at the end of a table cause locking ? We have a semi-emergency and would rather make a change now instead of waiting for downtime.
May 26, 2009 at 11:09 am
The addtion of a column by itself will be very quick. However, if you are creating it as NOT NULL and adding a DEFAULT constraint or something like this, the data update could take a bit of time if you have a significant amount of data in this table.
A.J.
DBA with an attitude
May 26, 2009 at 12:13 pm
homebrew01 (5/26/2009)
Would adding a new field at the end of a table cause locking ?
Yes. Adding a column will take a schema modification lock, which is incompatible with all other locks. How long the lock will be held for is another matter. Have you tested on a dev server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2009 at 12:39 pm
No I didn't test it in dev. It was a bit of a rush. Took a chance and added the column and updated it later with data. Got lucky and all is ok.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply