SYMPTOMS
When you use Data Definition Language (DDL) to modify a table, and then you try to save the table in Microsoft SQL Server 2008, you may receive the following 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.
CAUSE
This problem occurs when the
Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:
- You change the Allow Nulls setting for a column.
- You reorder columns in the table.
- You change the column data type.
- You add a new column.
WORKAROUND
To work around this problem, use Transact-SQL statements to make the changes to...To work around this problem, use Transact-SQL statements to make the changes to the metadata structure of a table.
Important We strongly recommend that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the "More information" section.
Important We strongly recommend that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the "More information" section.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are li...
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
To determine whether the Change Tracking feature is enabled for a table, follow these steps:
When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.
To change the Prevent saving changes that require the table re-creation option,...To change the
Prevent saving changes that require the table re-creation option, follow these steps:
- Open SQL Server Management Studio (SSMS).
- On the Tools menu, click Options.
- In the navigation pane of the Options window, click Designers.
- Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.
Risk of turning off the "Prevent saving changes that require table re-creation" option
To determine whether the Change Tracking feature is enabled for a table, follow these steps:
- In SQL Server Management Studio, locate the table in Object Explorer.
- Right-click the table, and then click Properties.
- In the Table Properties dialog box, click Change Tracking.
When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.
Steps to reproduce the problem
- In SQL Server Management Studio, create a table that contains a primary key in the Table Designer tool.
- Right-click the database that contains this table, and then click Properties.
- In the Database Properties dialog box, click Change Tracking.
- Set the value of the Change Tracking item to True, and then click OK.
- Right-click the table, and then click Properties.
- In the Table Properties dialog box, click Change Tracking.
- Set the value of the Change Tracking item to True, and then click OK.
- On the Tools menu, click Options.
- In the Options dialog box, click Designers.
- Click to select the Prevent saving changes that require table re-creation check box, and then click OK.
- In the Table Designer tool, change the Allow Nulls setting on an existing column.
- Try to save the change to the table.