SSMS – problem when modifying an existing table

When making changes to an existing table using SSMS i.e adding a column or changing data type of a column, SSMS will create a temporary table and copy all the data from the original table to the temporary table. Imagine if the table has one million rows 🙂 . Another problem that could occur:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created…”.

The latter can be solved by going to Tools menu – Options – Designers – Table and Database Designer and un-check the check box button that says “Prevent making changes that require table re-creation”.

Another way to solve it (and also avoiding the creation of temp table) is by using Alter Table statement and run it in query editor.

ALTER TABLE <table name> ADD <column name>  int NULL;