adding a new column to a large table

SQL Server 2000
Windows 2000 Server
I need to add a new column to a table that will likely have millions of rows
at most customer sites. The table currently has one FK and 2 unclustered
indexes. The new column is an int column. When I run the script to add the
column on our test server (table has about 5 million rows) it takes about
1/2 hour. Unfortunately, we can't have that kind of down time on that
table.
Is there a typical method for doing this more efficiently? Would it help to
drop the indexes before and recreate them after or would that end up taking
the same amount of time anyway? What about creating a temp table, copying
that data into the new table and renaming it?Are you trying to add this column in the middle of the other columns, or at
the end of the other columns? If the middle, try adding to the end and see
if that sppeds things up, Im betting thats whats going on.
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Here's the script:
ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
DF_Images_Version DEFAULT 1
I'd guess that adds to the end, no?
"ChrisR" <noemail@.bla.com> wrote in message
news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
> Are you trying to add this column in the middle of the other columns, or
> at the end of the other columns? If the middle, try adding to the end and
> see if that sppeds things up, Im betting thats whats going on.
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>|||Yes it does. I just realized you are adding these to customer sires. Are you
logging onto the boxes locally, or over the WAN?
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Here's the script:
> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
> DF_Images_Version DEFAULT 1
> I'd guess that adds to the end, no?
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>|||We haven't run the script on an actual customer site yet, only on our test
server which is a close resemblance. When we upgrade the customers, we will
likely remote in and run the script on the actual server.
(thanks for taking the time to help)
"ChrisR" <noemail@.bla.com> wrote in message
news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
> Yes it does. I just realized you are adding these to customer sires. Are
> you logging onto the boxes locally, or over the WAN?
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>|||I just realized...what about the default value? It has to apply that
default value to all the existing rows. Maybe that's what's taking a long
time. I wonder if disabling some level of logging during this update would
speed that up.
"ChrisR" <noemail@.bla.com> wrote in message
news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
> Yes it does. I just realized you are adding these to customer sires. Are
> you logging onto the boxes locally, or over the WAN?
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>|||Yes, the defualt value will take a bit. But it doesnt seems to me like Ive
ever experienced that severe of slowness, which is what led to my other
questions. Creating with NULL will be much faster of course... but still
thats a long time. I guess try just to add with NULL for timing tests.
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:eLFnFbhZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>I just realized...what about the default value? It has to apply that
>default value to all the existing rows. Maybe that's what's taking a long
>time. I wonder if disabling some level of logging during this update would
>speed that up.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||On Tue, 31 May 2005 10:50:16 -0600, Joel Lyons wrote:

>Here's the script:
>ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>DF_Images_Version DEFAULT 1
Hi Joel,
You're right (in your other post) - adding the default is what takes
time.
Consider breaking it in smaller steps:
1. Add a column, keep it nullable - should be fast.
ALTER TABLE dbo.Images
ADD Version tinyint
go
2. Fill the default value. This will take time, but if you use smallish
batches to process the data, locks will be released in between, so your
other applications will be able to proceed. If you have full recovery
model, then make sure that you back up the transaction log in between
(unless you don't mind it growing to accomodate all the changes).
SET ROWCOUNT 50000 -- experiment to get the optimal setting
DECLARE @.rc int
SET @.rc = 1
WHILE @.rc > 0
BEGIN
UPDATE dbo.Images
SET Version = 1
WHERE Version IS NULL
SET @.rc = @.@.ROWCOUNT
-- BACKUP LOG MyDatabase TO MyDatabaseLog
END
SET ROWCOUNT 0 -- never forget to reset ROWCOUNT!!
3. When all rows have been filled, set the NOT NULL constraint and the
DEFAULT:
ALTER TABLE dbo.Images
ALTER COLUMN Version tinyint NOT NULL
ALTER TABLE dbo.Images
ADD DEFAULT 1 FOR Version
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When you allow nulls and do not provide a default value, it will be very
very quick, because the new rows are not materialized until the page is
re-written... It works sort of like a virtual column... when you provide a
default value, the values must ACTUALLY be written, so the time it would
take is relative to the IO speed.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Very interesting info - thanks all!
I'll try some of these ideas and let you know.
-Joel
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>