Saving changes after table edit in SQL Server Management Studio

Question

If I want to save any changes in a table, previously saved in SQL Server Management Studio (no data in table present) I get an 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."

What can prevent the table to be easily edited? Or, is it the usual way for SQL Server Management Studio to require re-creating table for editing? What is it - this "option Prevent saving changes"?

Answer 1

To work around this problem, use SQL statements to make the changes to the metadata structure of a table.

This problem occurs when "Prevent saving changes that require table re-creation" option is enabled.

Source: Error message when you try to save a table in SQL Server 2008: "Saving changes is not permitted"

Answer 2

Go into Tools -> Options -> Designers-> Uncheck "Prevent saving changes that require table re-creation". Voila.

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work."

Answer 3

Many changes you can make very easily and visually in the table editor in SQL Server Management Studio actually require SSMS to drop the table in the background and re-create it from scratch. Even simple things like reordering the columns cannot be expressed in standard SQL DDL statement - all SSMS can do is drop and recreate the table.

This operation can be a) very time consuming on a large table, or b) might even fail for various reasons (like FK constraints and stuff). Therefore, SSMS in SQL Server 2008 introduced that new option the other answers have already identified.

It might seem counter-intuitive at first to prevent such changes - and it's certainly a nuisance on a dev server. But on a production server, this option and its default value of preventing such changes becomes a potential life-saver!

Answer 4

Rather than unchecking the box (a poor solution), you should STOP editing data that way. If data must be changed, then do it with a script, so that you can easily port it to production and so that it is under source control. This also makes it easier to refresh testing changes after production has been pushed down to dev to enable developers to be working against fresher data.

Answer 5

This is a risk to turning off this option. You can lose changes if you have change tracking turned on (your tables).

Chris

http://chrisbarba.wordpress.com/2009/04/15/sql-server-2008-cant-save-changes-to-tables/