February 27, 2006 at 8:30 am
Can anyone help.
Platform SqlServer 2000 (standard) on Win 2003 server.
50gb database.
I want to issue following table command to add new column to table:
ALTER TABLE [dbo].[AuditCollection] ADD [BinaryData] [image] NULL
This works fine on a test server with 1.2 million rows in the table and takes less than 1 second.
I need to apply this to our production server (24/7 web access) which has 14 million rows.
I had assumed that this action would require an exclusive table lock but I can find nothing in BOL to suggest that "ALTER TABLE ...ADD [new_column]" acquires such a lock. Sounds too good to be true, can anyone confirm this is so. Or am I walking over the cliff?
I would really like to apply this change ASAP without scheduling down-time 'cos it's a really active OLTP 24/7 system.
My big concern is an exclusive table lock causing build up of requests > disk queing > etc.etc > new job hunt.
Any advice on where to get more (on-line) detailed help on this would much appreciated.
Cris Yarker
February 27, 2006 at 10:42 am
from the books online
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to ensure no other connections reference even the meta data for the table during the change. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects a large number of rows.
February 27, 2006 at 12:38 pm
My past experience has been that adding rows to a large table takes a fraction of a second. I've always found this strange, and can't reasonable explain it.
I'd recommend either testing it on a copy (backup & restore--on same or different server) of the database, or make a copy of the table structure and populate it with "dummy" data (same size but for actualy contens), and see what happens on that. To me, it seems likely that this is a zero-time operation, but for 24x7xmanyG you'd want to be sure.
Philip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply