Sql Server 2008 Prevent Saving Changes That Require Table to be Re-created

I am new to Microsoft Sql Server 2008 and yesterday I had to make some changes to design of a table. I opened the table in edit design mode, made the changes and clicked on Save icon. But instead of saving the db changes, Sql Server 2008 showed the error message

“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.”

This same error displays in the following cases we well:

  • When we try to change the Allow Nulls setting of a column
  • On reorderiing the columns of a table
  • While changing datatype of a column
  • If we try to add a new column

Here are the steps to change the setting, to fix this.

  1. In Visual Studio, click on Options menu.
  2. Click on Designers
  3. In table options, UN-CHECK the checkbox against “Prevent saving changes that require table re-creation.”
  4. Click Ok button

Here is the image showing the setting to fix this.

Screenshot of Sql Server 2008 -AS I Had II

Sql Server 2008 -AS I Had II

One of the ways to continue working without changing the above option/setting is that we prepare a script of the changes and then run the SQL queries