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

Question

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

dastagiri16

Hall of Fame

Points: 3544
More actions
September 2, 2014 at 3:37 am

#292822

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

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

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 5

dastagiri16

Hall of Fame

Points: 3544

September 3, 2014 at 9:46 pm

#1743068

Thanks for all