Prevent Saving Changes in SQL 2008

Prevent Saving Changes is a new option that is introduced in SQL Server 2008. This option helps in preventing the unexpected changes that can happen on a table which includes dropping and re-creating the table.

By default, Prevent Saving Changes Option will set to ON in SQL Server 2008.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the below 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.”

Demo:
Create a table named Tbl_PreventSavingChanges
            1. Expand the Database AdventureWorks.
            2. Right-Click on the Tables and select New Table

            3. Save the Table

Now change the data type of the column ChangingColumn from nchar(10) to nvarchar(10)

And click on save button and notice an error popping out

Now to overcome this error, first click on cancel button and come out of the error message.

            1. Go to "Options" under the "Tools" menu

            2. In the options, expand “Designers” and select “Table and Database Designers”

            3. Now, Uncheck the option “Prevent saving Changes that require table re-creation”

            4. Click OK

            5. Save the table. Now you will be able to save the table.

This article is also available in pdf format for downloading.

Please Click here to get your copy.