If you modify structure of database table which requires the table to be recreated and try to save it in SQL Server 2008, you may receive an error message. For example, I modify Categories table in Northwind database by check AllowNulls on CategoryName column as the figure below.

Then, I try to save changes of the table, I will see the error message as following: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 when you click cancel, you will see this message dialog.User canceled out of save dialog

This error message occurs because of the default option on SQL Mangement Studio which prevents you to save changes on a table that require the table re-creation. So why prevent this? Because there may be a chance of data loss when you save changes on a table if the table need to be re-created. You can read about this by follow the link on reference section at the end of this post.

Microsoft recommends you to use Transact-SQL statements to make the changes of a table. But if you don’t want to write such statements, or you are working on test environment, or you do have a backup of database, you can simply turn off this option by follow instruction below.

5 Comments

Bob MarleySeptember 19, 2012

I wonder who it is at Microsoft who turns up to the meetings and says ‘Out of the box absolutely nothing should work, people love seeing error messages instead of results and enjoy tracking down solutions to problems that shouldn’t exist, they will thank us for making software that doesn’t allow them to hurt themselves’. Damn I want this fools blood.

Chris BarbaJuly 17, 2013

Yeah I found this annoying. Why make it difficult?
I could see turning off this setting on production servers, but not on everything.