Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Why is re-ordering a column name so slow?

Author  Topic 

waveform
Yak Posting Veteran

93 Posts

Posted - 2015-04-03 : 02:41:50
In SSMS, when designing tables: If I drag a column name to a new position, or insert a new column anywhere other than at the very end of the column list, clicking save ends up taking a long time - it seems the entire table needs to be dropped and recreated.

Why is it such a big deal to re-order a column name? This also applies to something like changing the "allow nulls" status. I'm curious why seemingly innocuous changes like that require so much work for SQL Server?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-04-03 : 06:36:43
That is indeed what happens: a new table with the new column order is created and the data copied (taking extra space as well as time). Adding a column is usually a metadata-only operation and finishes immediately. You can see the exact operations by using the Script feature in SSMS (before clicking OK). Since you can SELECT your columns in any order in a query, there's no need to reorder them in the table.

Changing a column to NOT NULL requires checking the data for any nulls, and it will fail if it finds any. If you also add a default value to that column, it needs to be written to every row (this has been optimized in SQL 2012 to avoid the write).
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-04 : 05:34:02
You can do it manually by executing the sp_rename procedure.

EXEC sp_rename 'dbo.Table1.ColOldName', 'ColNewName';



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2015-04-06 : 14:31:33
If you look at the script generated when you make those changes - the whole operation is performed in a transaction. That is...the new table is created with the new columns, then the new table is populated using an INSERT, and finally the old table is dropped and the new table renamed to the old table.

Once everything completes successfully - the transaction is committed.

For a large table that is going to take a long time and the old table will be unavailable.

If you can insure the table being modified will not have any new data added or modified, you can modify the script and remove the transaction. That will help some...other changes I use are to keep the old table around. Once all data has been populated, instead of dropping the old table - I rename it and then rename the new table to the old name.

As stated previously, adding new columns to the end of the definition is better as it is only a meta-data change and should not affect your ability to query the table. However, having worked with some systems that required columns to be ordered - I understand the requirement.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-07 : 08:01:17
quote:
Originally posted by SwePeso

You can do it manually by executing the sp_rename procedure.

EXEC sp_rename 'dbo.Table1.ColOldName', 'ColNewName';




I think the O/P is just trying to change the sequence of columns in the table
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-07 : 08:03:13
quote:
Originally posted by waveform

If I drag a column name to a new position, or insert a new column anywhere other than at the very end of the column list, clicking save ends up taking a long time - it seems the entire table needs to be dropped and recreated


The answer is to only "Add" new columns at the end of the column list.

Is there a reason that you want the columns in a particular order? (Its generally regarded as "unnecessary" in a SQL Table, although I must admit I like to group columns which are "logically associated with each other" together. I prefer to have Address1, Address2, ..., Address5 next to each other in the table rather than find that Address5 was added later WAY down the column list.)
Go to Top of Page
   

- Advertisement -