SSMS Tips and Tricks 1-9: Turn off option to prevent saving changes that require table recreation

I don’t use the table designer in SQL Server Management Studio. Sorry, but I just don’t like it, or the options that it chooses for me. I’d rather use T-SQL every time, but I’m also the first to admit that there are plenty of people who would use that designer.

And when they do, many run into an error that says:

Saving changes is not permitted. The changes that 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 option Prevent saving changes that require the table to be re-created.

And they throw up their hands in horror. According to the online documentation, this is typically because they’ve done one of these types of things: -Change the Allow Nulls setting for a column -Reorder columns in the table -Change the column data type -Add a new column

Having to recreate a table for things like changing a column’s data type seems way too heavy-handed. SQL Server Management Studio takes a safe option here. You can override it by doing this:

In Tools > Options > Designers > Table and Database Designers

While you can turn that off, be very careful if you decide to do so. You can cause issues, particularly if you are also using Change Tracking. (Existing tracking information is deleted when the table is recreated). You could also affect DDL triggers and many other things. For simple situations, this might help.

As I mentioned, if it was me, I’d use T-SQL statements that do just the actions that I need.

2025-05-25