Prevent Saving Changes in SQL 2008
Prevent Saving Changes is a new option being introduced in SQL Server 2008. This option helps in preventing the unexpected modifications that can happen on a table which includes dropping & re-creating the table.
By default, Prevent Saving Changes Option will set to ON in SQL Server 2008.
When you modify a table so that you alter the metadata structure of the table, & then you save the table, the table must be re-created based on these modifications. This may aftereffect in the loss of metadata & in a direct loss of data during the re-creation of the table. If you accredit 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 accept require the following tables to be dropped & re-created. You accept either 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.”
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 can be downloaded from the attachment.