When you try to modify the structure of a table that requires dropping and recreating a table to save the changes, like adding a new column between existing columns, changing column nullability or column data type in an existing table in SQL Server 2008 Management Studio Designer, you will get the following error message:
Saving changes is 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.
After clicking the cancel button you will receive the following dialog box:
User canceled out of save dialog
You will get this error message only if you attempt to make changes using SQL Server Management Studio, the same operation will work using T-SQL in Query Editor.
Modifying the structure of a table that requires dropping and recreating a table is very resource intensive and causes locks, so be careful when disabling this option. To disable it in Management Studio, on the Tools menu, click Options:
Select Designers:
Uncheck the “Prevent saving changes that require table re-creation” check box (by default, this option is checked):
No comments:
Post a Comment