Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
That means, if we add the column in end of table via wizard, it won't follow above process.. –  LolCoder Mar 14 '13 at 8:17
1  
@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. –  Martin Smith Mar 14 '13 at 8:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.