Adding column from SSMS wizard in SQL Server

Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process:

• Create a New Table with new columns

• Insert Data from previous table to new table

• Redo all the keys and constraints

• Drop old table Rename

• the new table to use the old table’s name

Why does SQL SERVER take such time and resource consuming process? Is there any benefit from such long process....

It only does that if you try and insert the new column between already existing ones.

If you add it to the end of the table it will just do a simple

ALTER TABLE T ADD some_column INT

The reason SSMS does this is because there is no TSQL syntax to reorder the columns in a table. It is only possible by creating a new table.

I agree that this would be a useful addition. Sometimes it would make sense for the new column to be shown next to an existing column when viewing the table definition (e.g. to have a created column next to a last_modified column or telephone_number grouped with address and email columns).

But a related Connect Item ALTER TABLE syntax for changing column order is closed as "won't fix". The only workaround apart from rebuilding the table would be to create a view with your desired column order.

StackExchange Database Administrators Q#36651, answer score: 5