is there any downtime required for adding new column for a large table..and adding a newe table

  • is there any downtime required for

    adding new column for a large table..or adding a new table

  • I think the large table will be locked while the new column is added. Try it for yourself on a test server, and see what locks are acquired. Adding a new table won't affect anything.

    John

  • Adding new table doesn't require downtime at all.

    If your queries are not using SELECT * then you are ok to add new column (as it will not affect your application functionality). To check locks while adding new column, you should add column in your test server & see the result.

    Adding column will lock the table.

    A table, as a whole, has a single schema (set of columns, with associated types). So, at a minimum, a schema lock would be required to update the definition of the table.

    Thanks

  • Hardy21 (9/2/2014)


    Adding new table doesn't require downtime at all.

    If your queries are not using SELECT * then you are ok to add new column (as it will not affect your application functionality). To check locks while adding new column, you should add column in your test server & see the result.

    Adding column will lock the table.

    A table, as a whole, has a single schema (set of columns, with associated types). So, at a minimum, a schema lock would be required to update the definition of the table.

    The second paragraph of that is copied from here, and if you read through that thread, your answer isn't strictly correct.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Adding a new table, no. If we're just talking about adding a blank table to the database and nothing else.

    Adding that column to a table, well, it depends. If it's nullable and you don't have a default value, you'll see some locks, but it's likely they won't be long. If it's non-nullable, you have to add a default value (or migrate the data through a new table and then switch them) and that's going to absolutely cause some serious locking.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for all

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply