Warning: "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 cant be re-created or enabled the option Prevent saving changes that require the table to be re-created"
This is the warning which I faced recently while saving the alterations to the existing table in the design mode.On doing R&D I got the actual process SQL Server Management Studio performs, which is as follows-
Incase we are adding a new column to the existing table, the following operations takes place:
When a new column is added SQL Server Management Studio renames the existing table to temp table.
After that, it creates a new table with new specification.
(Now, SQL Server Management Studio has two tables, copy of previous table with data and empty
table with new structure.)
SQL Server Management Studio 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.
As this entire operation is very resource intensive and puts lock on SQL Server tables in operation, eventually hanging SQL Server Management Studio, therefore SQL Server Management
Studio prevents user from running any operation that goes through the above-mentioned process.
But this feature causes problem for someone in saving the table changes. Therefore inorder to avoid this feature we can make changes in the SQL Server Management Studio as follows:
Menu >> Tools >> Options >> Designers >> Uncheck Prevent Saving changes that require table re-creation.
First go to-
Menu then click Tools then click Options then click Designers in the navigation Menu corresponding to this Uncheck the option "Prevent Saving changes that require table re-creation"