Adding columns to table with 3 millions rows

We are going to add about 3 or 4 columns to a table with 3 million rows toda
y.
We are doing this in production - after appropriate backups.
We are going to make sure the users that touch this table are kept out till
ENTERPRISE MANAGER saves the change.
We are goingto allow users that work other tables to stay on the DATABASE.
Is this reasonable?
Also - should we drop the 6 ALTERNATE INDEXES that are on this table before
we do the COLUMN adds with EM - then put them back later?First of all, you shouldn't be doing such a production change, using
Enterprise Manager (more info:
http://vyaskn.tripod.com/sql_enterp...er_or_t-sql.htm )
You should use the ALTER TABLE command to do this properly. First write your
ALTER TABLE commands, test them in your test environment, then schedule an
outage*, run the script with ALTER TABLE commands in production.
* Are you providing a default value for the new columns? or simply leaving
them as NULLs? When providing a default value, SQL Server will have to
update the new column with that default value, for all the rows, and that
will take a while.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:5498CB79-5124-41B5-B4A9-0887EE5EF19C@.microsoft.com...
We are going to add about 3 or 4 columns to a table with 3 million rows
today.
We are doing this in production - after appropriate backups.
We are going to make sure the users that touch this table are kept out till
ENTERPRISE MANAGER saves the change.
We are goingto allow users that work other tables to stay on the DATABASE.
Is this reasonable?
Also - should we drop the 6 ALTERNATE INDEXES that are on this table before
we do the COLUMN adds with EM - then put them back later?|||ALTER TABLe won't permit ADDING COLUMNS in the middle of the TABLE easily
though - right? I know EM does this by some truly hideous table
copy/manipulations.
Some of these are going to be MONEY fields - we want zeroes in them - so we
were planning on defaults.
We don't mind doing it in production - we can keep everyone out. We can
always restore the DB from the backup if we aren't happy with the results.
We expect it to take hours...
"Narayana Vyas Kondreddi" wrote:

> First of all, you shouldn't be doing such a production change, using
> Enterprise Manager (more info:
> http://vyaskn.tripod.com/sql_enterp...er_or_t-sql.htm )
> You should use the ALTER TABLE command to do this properly. First write yo
ur
> ALTER TABLE commands, test them in your test environment, then schedule an
> outage*, run the script with ALTER TABLE commands in production.
> * Are you providing a default value for the new columns? or simply leaving
> them as NULLs? When providing a default value, SQL Server will have to
> update the new column with that default value, for all the rows, and that
> will take a while.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:5498CB79-5124-41B5-B4A9-0887EE5EF19C@.microsoft.com...
> We are going to add about 3 or 4 columns to a table with 3 million rows
> today.
> We are doing this in production - after appropriate backups.
> We are going to make sure the users that touch this table are kept out til
l
> ENTERPRISE MANAGER saves the change.
> We are goingto allow users that work other tables to stay on the DATABASE.
> Is this reasonable?
> Also - should we drop the 6 ALTERNATE INDEXES that are on this table befor
e
> we do the COLUMN adds with EM - then put them back later?
>
>|||> ALTER TABLe won't permit ADDING COLUMNS in the middle of the TABLE easily
> though - right?
Why do you care where the columns end up? Your code shouldn't.
http://www.aspfaq.com/
(Reverse address to reply.)|||Call me obsessive.
But I would really an answer or two to the original post - anyone - please?
"Aaron [SQL Server MVP]" wrote:

> Why do you care where the columns end up? Your code shouldn't.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||> I know EM does this by some truly hideous table
> copy/manipulations.
Yes, see http://www.aspfaq.com/2528|||I'm aware of what is does in the background.
See the problem here is I'm an outside developer - this customer has no DBA.
So with that said, I could care less how long it really takes.
Will dropping the alternate indexes help - I guess it would - but I was
hoping for an answer.
I'm tempted to tell them to DROP the whole table - recreate it and load it
back with the data from a "saved" table.
"Aaron [SQL Server MVP]" wrote:

> Yes, see http://www.aspfaq.com/2528
>
>|||On Wed, 11 Aug 2004 05:13:03 -0700, Steve Z wrote:

>ALTER TABLe won't permit ADDING COLUMNS in the middle of the TABLE easily
>though - right? I know EM does this by some truly hideous table
>copy/manipulations.
(snip)
Hi Steve,
As Aaron already said, the column order shouldn't really matter.
But if you're facing a pointy-haired boss who doesn't understand zilch
about relational database but still insists on interfering with your work,
you can at least dump EM and use QA instead, using slightly less hideous
code. Something like this:
SELECT Col1, Col2, ...
INTO CopyOfOldTable
FROM MyTable
go
ALTER TABLE OtherTable
DROP CONSTRAINT FK_To_MyTable
go
DROP TABLE MyTable
go
CREATE TABLE MyTable
(Col1 ...,
NewCol ...,
Col2 ...,
..,
CONSTRAINT PK_MyTable PRIMARY KEY (...),
CONSTRAINT OtherConstraint ...)
go
INSERT MyTable (Col1, NewCol, Col2, ...)
SELECT Col1, 0, Col2, ...
FROM CopyOfOldTable
go
ALTER TABLE OtherTable
ADD CONSTRAINT FK_To_MyTable FOREIGN KEY (...) REFERENCES MyTable
go
--DROP TABLE CopyOfOldTable
--Commented out - you may wish to keep it for a while, just in case...
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
I'm thinking that we have to do it by copying data to a "holding" table -
dropping/re-createing the table with new format and then INSERT'ing into it
-
as you stated here.
Steve
"Hugo Kornelis" wrote:

> On Wed, 11 Aug 2004 05:13:03 -0700, Steve Z wrote:
>
> (snip)
> Hi Steve,
> As Aaron already said, the column order shouldn't really matter.
> But if you're facing a pointy-haired boss who doesn't understand zilch
> about relational database but still insists on interfering with your work,
> you can at least dump EM and use QA instead, using slightly less hideous
> code. Something like this:
> SELECT Col1, Col2, ...
> INTO CopyOfOldTable
> FROM MyTable
> go
> ALTER TABLE OtherTable
> DROP CONSTRAINT FK_To_MyTable
> go
> DROP TABLE MyTable
> go
> CREATE TABLE MyTable
> (Col1 ...,
> NewCol ...,
> Col2 ...,
> ...,
> CONSTRAINT PK_MyTable PRIMARY KEY (...),
> CONSTRAINT OtherConstraint ...)
> go
> INSERT MyTable (Col1, NewCol, Col2, ...)
> SELECT Col1, 0, Col2, ...
> FROM CopyOfOldTable
> go
> ALTER TABLE OtherTable
> ADD CONSTRAINT FK_To_MyTable FOREIGN KEY (...) REFERENCES MyTable
> go
> --DROP TABLE CopyOfOldTable
> --Commented out - you may wish to keep it for a while, just in case...
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||> I'm thinking that we have to do it by copying data to a "holding" table -
> dropping/re-createing the table with new format and then INSERT'ing into
it -
> as you stated here.
I still don't understand why column order matters.
I don't believe dropping the indexes will aid in efficiency of adding the
columns, since those indexes shouldn't be updated by the new columns...
If you already have 6+ indexes on the table, you might wish to drop them and
then, once the table has been modified, sit down and determine which
index(es) work best. I don't believe I have any tables in production with
that many indexes, but your business usage might be quite different than
mine.
The alter table should lock the table so that users can't modify the data
while the table is being changed. If you want to be extra safe, you could
use a serializable transaction around the operation.
http://www.aspfaq.com/
(Reverse address to reply.)