Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.

ERROR:

“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 error happens because “Saving Changes is not permitted” when using SQL Server 2008 Management Studio to modify the structure of a table especially when SQL Server needs to drop and recreate a table to save the structural changes. It is always advised to make a structural change for a table using TSQL. However, it is a convenient option for database developers to use SQL Server Management Studio make such changes  as the  Prevent Saving Changes That Require Table Re-creation  option is enabled by default in SQL Server 2008 Management Studio.

Disable “Prevent saving changes that require the table re-creation”

1.    Open SQL Server 2008 Management Studio (SSMS). Click Tools menu and then click on Options… as shown in the snippet below.



2.    In the navigation pane of the Options window, expand Designers node and select Table and Database Designers option as shown in the below snippet. Under Table Options you need to uncheck “Prevent saving changes that require the table re-creation” option and click OK to save changes.

This option when enabled prevents users from making structural changes to table using SQL Server Management Studio especially when SQL Server needs to recreate the table to save changes. By default, this option is checked and you need to uncheck this option to allow users to make any structural change through SSMS that   require table recreation.

A table needs to be recreated whenever any of the below   changes are made to the table structure.

·         Insert a new column in the middle of the table.

·         Add a new column in the table.

·         Change the Allow Nulls setting of a column.

·         Modify the identity property of a column.

·         Reorder the columns within a table.

·        Modify the datatype of a column.

Once you have disabled “Prevent saving changes that require the table re-creation” option you can go ahead and save the changes to the Employee Table. This will create a Validation Warning dialog box as shown in the below snippet. The warning message will be “One or more existing columns have ANSI_PADDING ‘off’ and will be re-created with ANSI_PADDING ‘on’”. Click “Yes” to save the changes.

Risks of Turing Off “Prevent Saving Changes that Require Table Re-creation” in SSMS

If you turn off this feature then you can avoid table recreation. However, if you have the Change Tracking feature of SQL Server 2008 enabled to track the table changes then the change tracking information will be lost completely whenever table is recreated. So, it is always advised to use this feature very carefully especially in a production environment.




Related Articles :

  • No Related Articles Found

5 Responses to “Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.”

  1. Quote your last paragraph: “If you turn off this feature then you can avoid table recreation.”

    IMO, this is not true i.e., if you click Yes, SQL will still re-create one or more columns of that table. By turning off the option, you acknowledge the risk of table/column re-creation and SQL allows you to do that.

  2. Is there any risk of data loss when changing the data type of a column and turning off the warning about table re-creation? I have a table that has many rows of data and I want to change a column from nvarchar(4000) to nvarchar(max).

    Thanks.

    Peter

    • masoud keshavarz Reply March 3, 2012 at 1:57 pm

      there IS a risk of data loss when changing DATA TYPE OF A COLUMN (only in some cases) but thre IS NOT risk of data loss when ADDING NEW COLUMN

      Your comment is awaiting moderation.

  3. I don’t understand the purpose or logic behind even having this setting.

    Say, I’m a database developer and I’ve decided I need to add an identity column to a table. I make the necessary change and click save, and then get the error message.

    In what universe would I say, “oh well, I guess since it won’t let me save, I won’t bother continuing this development process”, and just give up and go say to my boss, “sorry boss, I can’t make those new development changes because of this weird setting.”

    No, that’s never, ever going to happen, so why even bother adding the setting? Everyone, everywhere, is going to go into the IDE settings and uncheck the checkbox, so they can continue their development.

    The so called ‘good’ developers who write their database changes entirely in T-SQL from memory have the good fortune to be working for people with gobs of money to send them for training, upgrades, certifications, ad nausium.

    Us mere mortals, have to do battle with incessantly annoying IDE’s all while trying to make the boss happy and just get a job done.

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

    http://chrisbarba.com/2009/04/15/sql-server-2008-cant-save-changes-to-tables/

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |