I have a table with 2.5 million records and I want to add a new column to it in a certain place. I would normally do this in design view in Enterprise Manager, but with my table of this many records, it takes literally 30 minutes to save a 1 byte column. When I do this with "ALTER TABLE" statement, it does it immediately, but I cant put the column in between other columns?? So my question is either why is it so slow OR can I use the ALTER TABLE statement to place the column where I want?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Adding a new column
- Thread starter jepatte
- Start date
The reason it takes so long is that if you use and ALTER ADD it just tacks the new column on to the end. If you place it in a cpecific ordinal position using the design editor the result is
Create New Table design with a temp name
insert Data into new table from old table
drop old table
rename new table
build indexes
you can see the actual script if you save it as a script. you can then schedule the change to run at a non peak time.
Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
Create New Table design with a temp name
insert Data into new table from old table
drop old table
rename new table
build indexes
you can see the actual script if you save it as a script. you can then schedule the change to run at a non peak time.
Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
What Enterprise manager does is create a new table, populate it with your data and then drop the old table and rename the new one. You can see why this takes a long time. You should get out of the habit of ever using Enterprise Manager to alter a table.
No you cannot change the order of the columns using alter table, but the order of the columns in a table is irrelevant as you would query them in the order you want them. So unless you are using select * for all your queries (which you should not do for performance reasons), the order of the columns in the table is not a problem. Changing the column order can create difficulties for existing code too and so is to be avoided as a practice, especially if lazy programmers have used select * as the basis for insert statements. Because you wouldn't suddenly want the Social Security Number to be inserted into the last name column by accident.
"NOTHING is more important in a database than integrity." ESquared
No you cannot change the order of the columns using alter table, but the order of the columns in a table is irrelevant as you would query them in the order you want them. So unless you are using select * for all your queries (which you should not do for performance reasons), the order of the columns in the table is not a problem. Changing the column order can create difficulties for existing code too and so is to be avoided as a practice, especially if lazy programmers have used select * as the basis for insert statements. Because you wouldn't suddenly want the Social Security Number to be inserted into the last name column by accident.
"NOTHING is more important in a database than integrity." ESquared
fredericofonseca
IS-IT--Management
anyway. why would you wish to add a columns in a particular place on the list of columns?
It is you SELECT that should determine the order or columns, not the physical position on the table.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
FAQ219-2884
FAQ181-2886
It is you SELECT that should determine the order or columns, not the physical position on the table.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
FAQ219-2884
FAQ181-2886
- Thread starter
- #5
- Status
Similar threads
- Locked
- Question