Editing table structure in SQL

By default, SSMS will not let you insert a column in the middle of a table and save the changes.  This is because it requires dropping and recreating the table.

However, if you are OK with doing that, you don;t need to do it manually.  You can just shut off this validation in SSMS.

Tools > Options > Designers > Table and Database Designers
Clear the Prevent saving changes that require the table to be re-created check box.

It's that easy.


Actually, here are all the changes what this validation catches:

• Change settings allow nulls of a column.
• Rearrange the columns of the table.
• Change the data type of the column.
• Add a new column.

As for why this validation is there, it seems to mostly have to do with change tracking.
https://support.microsoft.com/en-us/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-changes-is-not-permitted