Saving changes after table edit in SQL Server Management Studio

sql sql-server ssms database-table

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

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

possible duplicate of Saving changes is not permitted in SQL Server

@Pang - This question came first, this is the duplicate.

@GrandMasterFlush meta.stackoverflow.com/a/252930/1402846 | meta.stackexchange.com/a/147651/204869

@Pang - I didn't know that, thanks.

Pedro

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

Microsoft Support site discourages this, but if you don't have any data in the table, I don't see the harm. Probably best to use TSQL to make the changes.

I would personally discourage using the designer for important databases. I have seen it make costly mistakes on many occasions. Besides, it promotes lazy development habits and allows people to modify database structure who may not be proficient enough to do so if they can't manage the SQL code route.

Agreed, Mark, yet in early development I don't need to write a pile of scripts.

It's shocking that it's September 2016, and this incredible user-unfriendly error (with no option to ask "Hey, would you like to ALLOW table changes ?") is still in play. Nope, you just get a Cancel button, and have to make your changes all over again. SQL Server at it's most stupid.

@Mike Gledhill Greetings from 2019 and this is still the case LOL

Pang

Tools | Options | Designers | Table and Database Designers

https://i.stack.imgur.com/hdWej.png

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

Thanks for the screenshot. I was looking for something like this to help me find that stupid option. The dialog should have a checkbox for "do it anyway" when it says you cannot.

The Best Practice After you change this option is to check the upper option 'Auto generate change scripts' in order to prevent data lose

Thanks for the Image. It's very useful :)

Daniel Vassallo

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"

My question here is: why does not SQL Server use the required T-SQL statements instead of dropping and recreating the table for every single change? I cannot understand this behavior.

HLGEM

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.

A) You can copy the script SSMS generates and use it on your production enviroment. B) SSMS is often easier, faster, and safer because SSMS handles all the details for you.

Itg is still a very bad idea. You do not want to recreate a 10,000, 000 record table on prod. It is not faster. How is it safer? Bad data is bad data and the consistency checks work if you use a script that you wrote as well as if you let SSMS write one. It is POOR practice in 100% of the cases.

I would say, in any setting where you're already in prod and there's data in it, then yes, that's probably true. On the other hand, in a case I was in, where I was still in development, and had a COMPLETELY BLANK TABLE I wanted to modify... I would argue less so. :p

Rolwin Crasta

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/

marc_s

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!

I'm now on a development server, but on a production one I will for sure turn it on back. Thanks once again for sharing experience

But, for example, changing the size of a nvarchar column from 100 to 120 is very straightforward operation that could be easily done with ALTER TABLE... then, why is SQL Server (Management Studio) dropping and re-creating the table for such cases?

@Jaime: that you need to ask the developers of that visual designer - no one else knows. It's just a fact - with the visual designer, many straightforward changes will always be done by re-creating the table and copying around. If you want to use the straightforward approach, it's up to you to handle it yourself by writing some easy T-SQL statements and executing them.

Thank You @marc_s This is exactly the answer I was expecting, though I had little faith in them having a hidden reason that would explain it all :)

This restriction also prevents the object id from changing without you knowing (in case you need that not to happen).

Md Wahid

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.

Saving changes after table edit in SQL Server Management Studio

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US