Can't save changes in SQL Server 2008 Management Studio table design?

March 27, 2013 Troubleshooting SQL

Made changes in the table designer but SQL Server 2008 Management Studio won't let you save?

Problem

This is a kind of annoying new feature in SQL Server 2008 Management Studio. In previous versions you could make changes in the design view of a table and if it wasn't possible to save these changes without dropping the table first you would be noticed about it and have the choice to do so. In the 2008 version you will just get a dialog that says it's not possible to save as it would require a drop:


"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."

Your only option is to cancel. Surprisingly this is actually by design.

Solution

To turn off this feature, go into the menu Tools / Options and under Designers uncheck the box for "Prevent saving changes that require table re-creation" and save. You will now be able to save your changes and the table will be dropped (loosing current data) to be re-created with your changes.


Remember to turn back on!It's pretty scary because with this off your table is dropped without any notice. In order to prevent future accidentally drops you should turn this feature back on before you shut down the studio.

Related posts: