308

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

Liam
  • 27,717
  • 28
  • 128
  • 190
rem
  • 16,745
  • 37
  • 112
  • 180
  • 4
    possible duplicate of [Saving changes is not permitted in SQL Server](http://stackoverflow.com/questions/6810425/saving-changes-is-not-permitted-in-sql-server) – Pang Oct 22 '14 at 08:37
  • 3
    @Pang - This question came first, this is the duplicate. – GrandMasterFlush May 04 '18 at 15:19
  • 1
    @GrandMasterFlush https://meta.stackoverflow.com/a/252930/1402846 | https://meta.stackexchange.com/a/147651/204869 – Pang May 05 '18 at 01:35
  • This is a risk to turning off this option. You can lose changes if you have change tracking turned on (your tables). – Chris Dec 30 '09 at 15:11

7 Answers7

660

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

Pedro
  • 11,514
  • 5
  • 27
  • 40
  • 1
    Thanks, Pedro. It seems Daniel was the first to answer, but +1 for clear and brief answer. – rem Dec 28 '09 at 13:44
  • 11
    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. – Jon Smock Feb 17 '10 at 16:38
  • 6
    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. – Mark W Dickson Jan 23 '14 at 17:40
  • 6
    Agreed, Mark, yet in early development I don't need to write a pile of scripts. – Kristopher Jan 12 '15 at 21:29
  • 12
    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 Sep 11 '16 at 18:12
  • 4
    @Mike Gledhill Greetings from 2019 and this is still the case LOL – Captain Kenpachi Oct 30 '19 at 07:54
  • 1
    Well, using this feature finally bit me in the back. SQL Management studio managed to drop and recreate the table in question just fine, but it failed to recreate all of the foreign keys linking said table (due to a timeout). I didn't expect it to fail partially, but that's what it did with FKs nowhere to be found. – jahu Jun 01 '20 at 10:17
  • 1
    I'm getting this error when trying to add a column to a brand new table. Why would that require dropping the entire table and re-creating it? – Daniel Arant Jun 04 '20 at 22:58
  • Thanks, I have to use MSSQL after a 10 years gap. I was already furious ... :D – DVarga Oct 08 '20 at 11:52
130

Tools | Options | Designers | Table and Database Designers

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

Pang
  • 9,564
  • 146
  • 81
  • 122
Prasanna
  • 4,583
  • 2
  • 22
  • 29
  • 7
    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. – Chris Benard Jun 21 '13 at 17:15
  • 2
    The Best Practice After you change this option is to check the upper option 'Auto generate change scripts' in order to prevent data lose – dubi Jan 27 '14 at 09:49
  • Thanks for the Image. It's very useful :) – M A. Feb 05 '14 at 07:48
71

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"

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 15
    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. – Jaime Oct 30 '13 at 15:13
17

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/

Rolwin Crasta
  • 4,219
  • 3
  • 35
  • 45
16

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 2
    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. – Trisped Oct 16 '14 at 17:27
  • 2
    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. – HLGEM Oct 21 '14 at 13:53
  • 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 – neminem Mar 05 '21 at 18:27
14

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    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 – rem Dec 28 '09 at 13:49
  • 2
    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 Oct 30 '13 at 15:10
  • 4
    @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. – marc_s Oct 30 '13 at 15:11
  • 1
    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 :) – Jaime Oct 31 '13 at 13:37
  • 1
    This restriction also prevents the object id from changing without you knowing (in case you need that not to happen). – Trisped Oct 16 '14 at 17:25
0

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.
Md Wahid
  • 450
  • 5
  • 12