Adding column from SSMS wizard in SQL Server

Question

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....

Answer 1

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.

• Akash KC: That means, if we add the column in end of table via wizard, it won't follow above process..
• Martin Smith: @LolCoder - Yes it oughtn't to. But you can always click the "script" icon in the toolbar to see what exactly it is going to do then sanity check it and run it yourself. By default SSMS won't actually do this anyway. You need to untick the box in SSMS options "Prevent saving changes that require table re-creation" to allow it to rebuild the table.