Saving changes after table edit in SQL Server Management Studio

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开发者_JS百科 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"?

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

Tools | Options | Designers | Table and Database Designers

Uncheck the option "Prevent saving changes that require table re-creation".

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"

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.

GO to SSMS and try this

Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that require table re-creation”.

Here is a very good explanation on this: http://blog.sqlauthority.com/2009/05/18/sql-server-fix-management-studio-error-saving-changes-in-not-permitted-the-changes-you-have-made-require-the-following-tables-to-be-dropped-and-re-created-you-have-either-made-changes-to-a-tab/

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!

Steps to reproduce the problem

1. In SQL Server Management Studio, create a table that contains a primary key in the Table Designer tool.
2. Right-click the database that contains this table, and then click Properties.
3. In the Database Properties dialog box, click Change Tracking.
4. Set the value of the Change Tracking item to True, and then click OK.
5. Right-click the table, and then click Properties.
6. In the Table Properties dialog box, click Change Tracking.
7. Set the value of the Change Tracking item to True, and then click OK.
8. On the Tools menu, click Options.
9. In the Options dialog box, click Designers.
10. Click to select the Prevent saving changes that require table re-creation check box, and then click OK.
11. In the Table Designer tool, change the Allow Nulls setting on an existing column.
12. Try to save the change to the table.