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.