locked
Add new column to production table RRS feed

  • Question

  • Hi,

    I'm relatively new to SQL.  I need to add a column to a key table but there are many users locking the table.  I normally have to as users to log off but I cant imagine this is the only option.

    The save operation times out when the table is locked.

    Is there a way to add/modify/delete a column at any time regardless of who is using the table?

    Thanks

    Mani

    Tuesday, June 4, 2013 3:35 PM

Answers

  • To add to this, if a DDL operation requires only a brief Sch-M lock it's referred to as an "online" operation.  It still must wait for exclusive access to the table, but it holds that exclusive access only for a moment, and users would never really notice.

    Online DDL has been enhanced in this scenario in SQL 2012, and adding a non-null column with a default is now an online operation in SQL 2012 Enterprise Edition.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Tuesday, June 4, 2013 3:48 PM
    • Marked as answer by Kalman Toth Wednesday, June 12, 2013 12:41 AM
    Tuesday, June 4, 2013 3:45 PM

All replies

  • Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.

    Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table. 

    better use alter table cmd rather that using table design mode

    alter table tbl add column1 int

    ct


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, June 4, 2013 3:41 PM
  • how wide this table is ( total sum of bytes in a ROW)

    how many records are there in this table.

    its better to use the command alter table add column instead using the SSMS designer as sometimes the SSMS may hang.


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

    Tuesday, June 4, 2013 3:43 PM
  • To add to this, if a DDL operation requires only a brief Sch-M lock it's referred to as an "online" operation.  It still must wait for exclusive access to the table, but it holds that exclusive access only for a moment, and users would never really notice.

    Online DDL has been enhanced in this scenario in SQL 2012, and adding a non-null column with a default is now an online operation in SQL 2012 Enterprise Edition.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Tuesday, June 4, 2013 3:48 PM
    • Marked as answer by Kalman Toth Wednesday, June 12, 2013 12:41 AM
    Tuesday, June 4, 2013 3:45 PM
  • Hi,

    Thanks for the tip.  Is this a fairly safe option?  I mean I don't want to end up losing the table or corrupting it which could be business critical.

    Thanks

    Mani

    Tuesday, June 4, 2013 4:35 PM
  • Adding a column with SSMS or with the ALTER TABLE ... ADD COLUMN command are both safe. Neither will corrupt or drop your table.

    The difference is, that SSMS might decide to create a script that creates a new table according to your new table definition, copy all data, drop the old table and rename the new table. It will do all this in a transaction. If it chooses to do so, it can take a very long time. If it fails, the transaction will be rolled back (which can also take a long time). Worst case, you will have to wait very long.

    If you use ALTER TABLE ... ADD COLUMN, then the operation is instantaneous after acquiring the schema lock (which may take a few second or minutes, depending on other processes that lock the table). If it fails, then basically nothing has happened / changed.


    Gert-Jan

    Tuesday, June 4, 2013 6:12 PM