Have you ever tried to add a column to a SQL Server Table using SQL Server Management Studio, only to be confronted with the following error message:
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the following Prevent saving changes that require the table to be re-created.
Not only that, but it forces you to cancel out of the dialog and you lose all of the changes you made. *Not cool*
While it is a best practice to always use TSQL when creating or altering the structure of Tables (so that you can save your scripts), it would be convenient on occasion to allow database developers to make small structural changes using SQL Server Management Studio.
Turns out, allowing a table to be dropped and recreated to alter its structure is an option you can configure in SQL Server Management Studio.
- Launch SQL Server Management Studio
- Choose Tools à Options
- On the left, expand Designers and select Table and Database Designers
- Uncheck the checkbox entitled Prevent saving changes that require table re-creation
- Click OK