Post reply

ALTER TABLE while in use ?

  • May 26, 2009 at 10:33 am

    #155746

    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

    #1000046

    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

    #1000089

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 26, 2009 at 12:39 pm

    #1000107

    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