I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it? I appreciate the help!
J8You're trying to add 150 million pieces of data (3 million rows times 50 columns) to your table, which now has 210 million pieces (3 million rows times 70 columns). That is significant growth (about 70%), so it will probably take quite a while.
If the columns are all NULL-able, then I'd create a second "child" table that had the primary key from the first table and all of the new columns in it. That would allow you to populate them much more gracefully, possibly in stages.
If you really need to add these columns to the existing table because of Referential Integrity issues or due to other reasons, then I'd strongly recommend declaring downtime (so you can force the users off of the system), then making the changes from Query Analyzer using the ALTER TABLE command. It still won't be fast, but it will be faster than any other method for doing this kind of job.
-PatP|||Pat,
For whatever reason, I have to stick those new columns into this big table. You can consider this table as kind of 'feed' table.
Thanks for the tips.
J8|||You should not have problems adding 50 columns providing they all allow NULL. If not then they must have a default, and you may bring your database down very easily (which what I suspect has happened) because every row must be updated with default value for that column or columns.|||You can script the addition of columns and run it from query analyzer
alter table mytable add col71 int null, col72 int null, ...
as long as all of the columns are nullable this should happen instantly. I would expect EM to add them instantly too unless there is some difference between the default ANSI settings of the DB, Table, and your EM Session.
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Tuesday, March 6, 2012
Saturday, February 25, 2012
Adding Identity Column to BIG table
I've got a table with 36+ million rows. I've been asked to modify the
table and add in an identity column. The code I used caused SQL to
lock up and it maxed out the log files. :)
table and add in an identity column. The code I used caused SQL to
lock up and it maxed out the log files. :)
The code I used is:
Begin Transaction
Alter Table ODS_DAILY_SALES_POS
ADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)
Commit
Is there a way to break up the code? Maybe only do a few million
records at a time? Or is there a way to do this without locking
anything up?
Thanks,
Jennifer>> I've been asked to modify the table and add in an identity column.
The code I used caused SQL to lock up and it maxed out the log files.
<<
Do you have any idea why anyone would want to do this in the first
place? The idiot does not seem to understand that IDENTITY has no
meaning in a data model?|||[posted and mailed, please reply in news]
Jennifer (jennifer1970@.hotmail.com) writes:
> I've got a table with 36+ million rows. I've been asked to modify the
> table and add in an identity column. The code I used caused SQL to
> lock up and it maxed out the log files. :)
> The code I used is:
> Begin Transaction
> Alter Table ODS_DAILY_SALES_POS
> ADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)
> Commit
> Is there a way to break up the code? Maybe only do a few million
> records at a time? Or is there a way to do this without locking
> anything up?
The alternative is to rename the table and all its constraints,
create the table and new with constraints, triggers and indexes
and insert the data into that table. You can then do a loop which
takes a reasonable number of rows at a time. That requires, however,
that you somehow, can identify which rows you have copied and which
you have not. An advice is to perform the loop on the clustered of
the table. Once data has been copied, move referencing foreigh keys
to point to the new table, and then drop the old table.
The advantage of this approach is that the strain on the log is less,
particularly, if you permit yourself to switch to simple recovery
while you are running the move.
Note: above I said that you should recreate triggers and indexes. It
may be a good idea to do that after the copying is completed, but
just don't forget it. (You should package everything in a script
and first test in database where the table is smaller.)
A variation is to bulk out the data, and then bulk it in when the
table has no indexes. If you have bulk_logged recovery, this load will
be very fast. Personally, I prefer to create the clustered index first,
before I load, since building the index takes its time too.
Yet a variation is to use SELECT INTO (with which you can use
the IDENTITY function). SELECT INTO is also minimally logged when
you have bulk_logged recovery.
Finally, you have set your colunm to bigint. With 36 million rows, you
have a long way to go, before you 31 bits become too few for you.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, February 24, 2012
Adding Fields To A Table
I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?How are you doing it now?|||Through the Management Studio interface. Modify the table, add the fields, save the table. I get a timeout expired message.|||are you adding them to the end of the table, or inserting between 2 other fields, the latter taking a longer time.
look at Alter Table...|||Did you save the script?
And are you moving the columns into places not that are the last
It will make a temp copy of the table, copy all of the rows, rebuild the new table, then copy the data over, then do an sp_rename, then drop the original
Lot of overhead
just do this
CREATE TABLE myTable99(col1 int IDENTITY(1,1), Col2 datetime DEFAULT(GetDate()), Col3 Char(1))
GO
INSERT INTO myTable99(Col3)
SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO
SELECT * FROM myTable99
GO
ALTER TABLE myTable99 ADD Col4 binary
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.|||see the last line in Bretts signature. this violates relational theory. your application should not act like this.|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.|||The problem you are facing is that Management studio is going to do the following to accomplish this:
1) Create a new table with the name tmp_yourtablename with all the columns in the order you want.
2) Transfer all of the data from the old table to the tmp_ table. Yes. all 15 million rows will be doubled up.
3) Drop the old table (usually with no error checking)
4) Rename the tmp_table as the original table name.
Contrast that with the alter table command which would append the two new columns on the end of the table, and takes a few seconds to run.
How can the archival process be simpler by using the field index? i would think that any of the columns "pushed out" by the insertion of new fields in the "middle" of the table to cause much larger problems.|||Some existing processes rely on the order of the fields.
Why would that be?
SELECT * perhaps?|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.
OK, so what does that have to do with not adding them to the end?
In any case, The logging is what's going to kill you
I might bcp the data out
CREATE the new table
bcp the data back in, using a format card, into the new table|||The archival is being done in Visual Basic, using ADO recordsets. When you reference the table's Fields collection using the index, as long as field 1 is ID in one table and 1 is ID in the other table then it doesn't matter the names of the fields, but the order is important.|||The problem with adding them to the end is, the only difference in the table structure is the last field in the archive table. It is the datetime the record was archived.|||The archival is being done in Visual Basic, using ADO recordsets.
Shoot me now|||How many indexes are on this table? You might consider dropping them before you add the new columns, and then recreating them.|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.
I believe SQL need to create a temp table of the table, then insert the data back when you insert, as oppose to add fields to the end of the table. ColIDs change when inserting new fields, as opposed to adding.
look at Alter Table...|||Did you save the script?
And are you moving the columns into places not that are the last
It will make a temp copy of the table, copy all of the rows, rebuild the new table, then copy the data over, then do an sp_rename, then drop the original
Lot of overhead
just do this
CREATE TABLE myTable99(col1 int IDENTITY(1,1), Col2 datetime DEFAULT(GetDate()), Col3 Char(1))
GO
INSERT INTO myTable99(Col3)
SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO
SELECT * FROM myTable99
GO
ALTER TABLE myTable99 ADD Col4 binary
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.|||see the last line in Bretts signature. this violates relational theory. your application should not act like this.|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.|||The problem you are facing is that Management studio is going to do the following to accomplish this:
1) Create a new table with the name tmp_yourtablename with all the columns in the order you want.
2) Transfer all of the data from the old table to the tmp_ table. Yes. all 15 million rows will be doubled up.
3) Drop the old table (usually with no error checking)
4) Rename the tmp_table as the original table name.
Contrast that with the alter table command which would append the two new columns on the end of the table, and takes a few seconds to run.
How can the archival process be simpler by using the field index? i would think that any of the columns "pushed out" by the insertion of new fields in the "middle" of the table to cause much larger problems.|||Some existing processes rely on the order of the fields.
Why would that be?
SELECT * perhaps?|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.
OK, so what does that have to do with not adding them to the end?
In any case, The logging is what's going to kill you
I might bcp the data out
CREATE the new table
bcp the data back in, using a format card, into the new table|||The archival is being done in Visual Basic, using ADO recordsets. When you reference the table's Fields collection using the index, as long as field 1 is ID in one table and 1 is ID in the other table then it doesn't matter the names of the fields, but the order is important.|||The problem with adding them to the end is, the only difference in the table structure is the last field in the archive table. It is the datetime the record was archived.|||The archival is being done in Visual Basic, using ADO recordsets.
Shoot me now|||How many indexes are on this table? You might consider dropping them before you add the new columns, and then recreating them.|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.
I believe SQL need to create a temp table of the table, then insert the data back when you insert, as oppose to add fields to the end of the table. ColIDs change when inserting new fields, as opposed to adding.
Monday, February 13, 2012
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.)
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.)
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_enterpr...r_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_enterpr...r_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:
> 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.)
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_enterpr...r_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_enterpr...r_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:
> 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.)
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!?!?!'!?
> >
> >
> >
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!?!?!'!?
> >
> >
> >
Subscribe to:
Posts (Atom)