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 table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

Today, we will delve into a very simple issue that one of the Jr. Developers at my organization confronted. I have a preference for T-SQL. According to me, all the developers should always use T-SQL instead of Design feature of SQL Server Management Studio (SSMS). In fact, sound knowledge of T-SQL has the potential to make a huge difference in the development of the developer. One issue with using design mode of SSMS is that it sometimes adds too much overhead to the actual code and locks up the complete database. In the earlier version of SSMS, it was quite common to see that even on doing a small change the whole SSMS got hanged and went unresponsive till the operation was over.

The reason for SSMS to go unresponsive is that when some operations (i.e. adding column, changing datatype of any column, etc) happen, it has to do much more work than the actual operation. Let us now examine an operation where we will add a new column to an already existing table.

  • Transaction starts.
  • When a new column is added SSMS renames the existing table to temp table.
  • After that, it creates a new table with new specification.
  • Now, SSMS has two tables: copy of  previous table with data and empty table with new structure.
  • SSMS now starts moving older data from older table to newer table.
  • If there is any error, it rolls back transaction.
  • If there is no error, it commits all the previous operations.
  • Transaction ends.

This entire operation is very resource intensive and puts lock on SQL Server tables in operation, eventually hanging SSMS.

SQL Server 2008 SSMS has new a feature that averts this issue. SSMS prevents user from running any operation that goes through the above-mentioned process. Let us see an example for the same.

First, create table using the following script in any database.

CREATE TABLE dbo.Table_1
(
ID INT NOT NULL,
First VARCHAR(50) NOT NULL,
Second VARCHAR(50) NOT NULL,
Third VARCHAR(50) NOT NULL
)
ON [PRIMARY]
GO

Once the table is created open the table in SSMS by clicking on the table name and selecting “Design.” Try to include another column to the existing table and click on save (CTRL+S). It will prevent it from saving and will emit the following error in popup.

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 table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

I like how SSMS takes stance of displaying warning message and stops user from going further to do operation, thereby preventing hanging of SSMS. However, this feature can cause inconvenience in a situation when one is required to save table using SSMS. If such situation arises, then this feature can be turned off by going to Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that require table re-creation”.

This restriction only applies to operation attempted using only SSMS. If modification of tables are attempted using T-SQL in Query Editor, it will work just fine!

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

63 thoughts on “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 table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

  1. Hi Pinal,
    Thanks for the useful tip, I shared it with my folks here. Kept looking when i got the error as to why it was happening. Now i get it. Currently working on SSRS 2008, great product, especially Report Builder 2.0 is a very powerful for the analysts, the charts especially are very professional looking.

  2. Sorry Boss, but I have to disagree with you on this one.

    Unless this is a one man shop, the developer isn’t making the change on the production server, it’s on the dev system.
    Then the staging and production servers should be scripted using some form of automation, not by hand.

    As far as scripts versus design, I’m thinking there’s a much greater margin of error cut-n-pasting and eye-balling scripts than getting it right the first time in the designer.

    (plus…let the developer improve at their own expense….not the company’s [snicker])

  3. Thanks for the great info and explanation! The process might demand some resources but it avoids human mistakes an makes life much easier. Great post!

  4. I’ve read similar posts on a dozen different websites, but none are addressing the most important concern.

    I need to add a column to a table contain a few million rows. After testing this solution on a test database, I find that it only takes about 30 seconds to complete; not a big deal.

    So is this safe to do on a production table after hours?

    Is there any risk of losing data of any kind (table data, constraints, stats, indexes)?

    I guess I don’t understand why MS locks you out in the first place. Is it solely because of the potential of hanging a production database for a long period of time (because its such an extensive operation)?

    Thanks for any insight!

  5. Thanks my man. I have had to Google over the years to solve SQL Server issues and have more than once been helped by your informative and well presented material. Tip of the hat and a big goodday to you!

  6. wow. your page is a lifesaver. I have learn more reading here on your pages that I have in some of my classes. thank you so much for posting all this Knowledge.

  7. I don’t understand the why there is a difference in safeguards between SSMS and T-SQL in the editor mode. So it won’t let me make a change in SSMS b/c I might screw something up, but if I do it using T-SQL then I’m always perfect? I will never make a mistake? Of course not. So why not block me from making changes using T-SQL as well?

    Second, if I make the change in SSMS, it could potentially hang the server for very large tables. But if I make the change using T-SQL it won’t hang the server? The change is just made instantaneously with no performance impact? If that is the case, why isn’t SSMS smart enough to take the change I made and create the T-SQL for me behind the scenes and execute it. Thus, there would be no performance impact. Again, this doesn’t make sense that SSMS affects performance but T-SQL can somehow magically change a database structure with no performance impact.

    Can you explain this better? I’m happy to know how to make the changes to me dev database with SSMS now, but the reasoning doesn’t seem right. Thanks.

  8. Actually Toad for SQL Server does a whole lot of a better job when it comes to designing tables and altering them without having to drop them and re-create them. Too bad it costs money and is not included with SQL Server, MS should have come up with something comparable in quality, SSMS is really poor in features and slow compared to TOAD which is a mind blowing piece of software for SQL Server development.

  9. Thanks Dave. This is a better explanation than I had read in other sites. I believe there is a valid reason for why Microsoft made preventing changes through SSMS the default. As I recall, when you make changes like this they don’t get saved to the log and therefore you may run into problems if you ever have to restore the database.

  10. Dave, I received a comment her on my blog and would like to make it clear that I put a source link in the post that makes reference to her.

    If necessary change the text of the post, but would like to notify you that I use my blog as a repository or library consultation only.

    Any problems please look me up.

    Sincerely, Zani

    My post: http://goo.gl/ltfWFA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s