CODEWITHSUNDEEP
sql-serverperformance
PiotrWolkowski
PiotrWolkowski

Reputation: 8782

Adding column at specific location vs at the end of data table

In SQL Server is adding a new column at the end of the table faster than adding it at a specific location?

I have to add 20 new columns to a table that already has 250 columns and roughly 2M records. The new columns are null, float columns. Adding single column took me more than 10 minutes. I'm looking for any way to speed up the process.

Upvotes: 0

Views: 1009

Answers (2)

cesta
cesta

Reputation: 34

If you're using SSMS, you should know that under the hood it is creating a new table with new columns then inserting the data from the original table, redoing all the keys and constraints, dropping the old table and then renaming the new table to use the old table's name.

It is probably better practice to add the row at the end and you can use then T-SQL in an order you want for retrieval.

Another note, if you're not locking the db or don't put it in single user mode it will take longer to run.

tl;dr - do not add column at specific position unless you have time to waste

Upvotes: 2

eddiecjc
eddiecjc

Reputation: 222

Are you using?: ALTER TABLE table_name ADD column_name datatype

Or are you using the SSMS designer? SSMS designer may drop and recreate the table (a temporary table is created to hold the data temporarily) - this may be why it is taking a longer time.

Upvotes: 2

Related Questions