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.
| 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). |
 |
|
|
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 |
|
|
|
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. |
|
|
|
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  |
|
|
|
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.) |
|
|
|
|
|
|
|
|
Why is re-ordering a column name so slow?