SQL SERVER – Fix : Management Studio Error : Saving Changes in 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 option Prevent saving changes that require the table to be re-created

Today, we will delve into a very simple issue that one of the Jr. Developers at my organization confronted. I have a preference for T-SQL. According to me, all the developers should always use T-SQL instead of Design feature of SQL Server Management Studio (SSMS). In fact, sound knowledge of T-SQL has the potential to make a huge difference in the development of the developer. One issue with using design mode of SSMS is that it sometimes adds too much overhead to the actual code and locks up the complete database. In the earlier version of SSMS, it was quite common to see that even on doing a small change the whole SSMS got hanged and went unresponsive till the operation was over.

The reason for SSMS to go unresponsive is that when some operations (i.e. adding column, changing datatype of any column, etc) happen, it has to do much more work than the actual operation. Let us now examine an operation where we will add a new column to an already existing table.

• Transaction starts.
• When a new column is added SSMS renames the existing table to temp table.
• After that, it creates a new table with new specification.
• Now, SSMS has two tables: copy of  previous table with data and empty table with new structure.
• SSMS now starts moving older data from older table to newer table.
• If there is any error, it rolls back transaction.
• If there is no error, it commits all the previous operations.
• Transaction ends.

This entire operation is very resource intensive and puts lock on SQL Server tables in operation, eventually hanging SSMS.

SQL Server 2008 SSMS has new a feature that averts this issue. SSMS prevents user from running any operation that goes through the above-mentioned process. Let us see an example for the same.

First, create table using the following script in any database.

CREATE TABLE dbo.Table_1
(
ID INT NOT NULL,
First VARCHAR(50) NOT NULL,
Second VARCHAR(50) NOT NULL,
Third VARCHAR(50) NOT NULL
) ON [PRIMARY] GO

Once the table is created open the table in SSMS by clicking on the table name and selecting “Design.” Try to include another column to the existing table and click on save (CTRL+S). It will prevent it from saving and will emit the following error in popup.

Saving Changes in 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 option Prevent saving changes that require the table to be re-created

I like how SSMS takes stance of displaying warning message and stops user from going further to do operation, thereby preventing hanging of SSMS. However, this feature can cause inconvenience in a situation when one is required to save table using SSMS. If such situation arises, then this feature can be turned off by going to Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that require table re-creation”.

This restriction only applies to operation attempted using only SSMS. If modification of tables are attempted using T-SQL in Query Editor, it will work just fine!

Reference : Pinal Dave (https://blog.sqlauthority.com)