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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: