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 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?First of all, you shouldn't be doing such a production change, using
Enterprise Manager (more info:
http://vyaskn.tripod.com/sql_enterprise_manager_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_enterprise_manager_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?
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:
> > 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.)
>
>|||> I know EM does this by some truly hideous table
> copy/manipulations.
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)|||> 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.)|||We decided to:
1) Backup the database
2) DROP the 6 indexes
3) Kicked users out of this part of the application
4) Use ENTERPRISE MANAGER to add 6 new columns - inserted into existing
position in the table
5) Pressed SAVE in EM - it took less than 1 hour to process
6) Put the 6 indexes back in place - that took 9 minutes and made two users
or other tables timeout - no big deal
People are back into production and processing medical claims against the
big table we changed.
Note: the log grew 1.7 gig - we expected that.
Although I had fear about using EM for this, I gotta tell you - it worked
flawlessly. And since it was really done by the IT person at the site here,
someone not even aware there is another method to go about it - I'm sure they
would use EM again in the future...
"Aaron [SQL Server MVP]" wrote:
> > 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.)
>
>|||> 4) Use ENTERPRISE MANAGER to add 6 new columns - inserted into existing
> position in the table
WHY!?!?!'!?|||It's really, really simple Aaron.
I'm a programmer - I could care less what column orders are what. I never
use SELECT * - we do all SQL in SPROCS. We are totally the best SQL shop we
can be.
But - and this is a large BUT - the customer - coming from a mainframe - was
sold on SQL because of how the other tools MS has (EXCEL for instance) and
other WINDOWS apps - Crystal - can access the data.
Users love to have columns in groups - it's a visual thing. I have no
control over that. If I tried to suggest having these columns not be next to
existing "money" columns in the claim table, I would get cross-eyed looks.
That's the reality. They all use GUI tools to touch the data - I don't -
they do.
"Aaron [SQL Server MVP]" wrote:
> > 4) Use ENTERPRISE MANAGER to add 6 new columns - inserted into existing
> > position in the table
> WHY!?!?!'!?
>
>|||Some of the workarounds I already pointed you to (such as creating a view)
might prevent you from re-organizing a table for no reason in the future.
;-)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1502A683-5D87-48F2-B789-9F132792A0DE@.microsoft.com...
> It's really, really simple Aaron.
> I'm a programmer - I could care less what column orders are what. I never
> use SELECT * - we do all SQL in SPROCS. We are totally the best SQL shop
we
> can be.
> But - and this is a large BUT - the customer - coming from a mainframe -
was
> sold on SQL because of how the other tools MS has (EXCEL for instance) and
> other WINDOWS apps - Crystal - can access the data.
> Users love to have columns in groups - it's a visual thing. I have no
> control over that. If I tried to suggest having these columns not be next
to
> existing "money" columns in the claim table, I would get cross-eyed looks.
> That's the reality. They all use GUI tools to touch the data - I don't -
> they do.
> "Aaron [SQL Server MVP]" wrote:
> > > 4) Use ENTERPRISE MANAGER to add 6 new columns - inserted into
existing
> > > position in the table
> >
> > WHY!?!?!'!?
> >
> >
> >