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

« SQLAuthority News – Gandhinagar SQL Server User Group Meeting – International Speaker Visiting

SQLAuthority News – SQL Server Energy Event – Mark Your Calendar – May 20, 2009 »

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

May 18, 2009 by pinaldave

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

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, SQLServer, T SQL, Technology | 50 Comments

50 Responses

on May 18, 2009 at 11:26 pm | Reply ramdas

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.
on July 26, 2009 at 8:37 pm | Reply Faisal Khan

Good job.
on September 22, 2009 at 10:42 am | Reply dreamerboy88

thanks very much for that
on December 1, 2009 at 2:32 pm | Reply amina khan

thnks alot:)
on December 8, 2009 at 4:29 am | Reply Donna

thank you! was getting tired of manually re-creating (copy/paste of columns), saving, deleting prior and renaming new.
on December 27, 2009 at 5:04 am | Reply Thanks

Thanks thisis useful
on December 29, 2009 at 1:23 pm | Reply William Plander

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])
on January 29, 2010 at 4:03 pm | Reply Ranga

Thanks a lot. this is helpfull
on March 18, 2010 at 9:35 am | Reply noname

oops, my option didn’t show designers so i couldn’t uncheck it. my studio is 2008 on window 2003
on April 23, 2010 at 2:56 am | Reply Srinu

Thanks. This is was very helpful
on May 7, 2010 at 9:23 am | Reply Ross Mistry

Thanks Boss – Very Helpful. Stumped the chumps
on July 23, 2010 at 3:22 pm | Reply Vladimir

Thanks. This is was very helpful
on July 24, 2010 at 4:07 pm | Reply Ehab

Thanks For this work
really good work
on August 23, 2010 at 10:42 pm | Reply Christian

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!
on September 9, 2010 at 3:54 pm | Reply Yathini

Thanks a lot….
on September 14, 2010 at 2:44 am | Reply Andy

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!
on September 16, 2010 at 3:00 pm | Reply A

Thank you very much.
It’s a great solution.
on December 20, 2010 at 3:44 pm | Reply Mahir

Thank you….. great sql developerr….
on May 16, 2011 at 11:59 am | Reply Muraleekrishnan

Thank You Very Much.This is very helpful
on August 5, 2011 at 5:57 am | Reply Boyd White

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!
on August 22, 2011 at 2:37 am | Reply Chris

Thank you.
on November 20, 2011 at 10:48 pm | Reply eddie

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.
on December 12, 2011 at 2:13 am | Reply Anis

Thanks !
on January 17, 2012 at 11:30 pm | Reply Leonard

Great! This was such a help. I was tired of deleting the table and recreating a new one every time I needed to add or change a column.
on February 28, 2012 at 9:30 am | Reply morningthanks

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.
on March 7, 2012 at 4:28 pm | Reply Afzaal ahmad

Thanks it Work for me
on March 29, 2012 at 2:08 am | Reply bbdginc

This worked perfectly. Kind of scary recreating the table when I only need to increase 1 fields size!
on April 6, 2012 at 8:47 pm | Reply labish

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.
on May 18, 2012 at 4:54 am | Reply msk5400

Thank you
on July 4, 2012 at 12:43 pm | Reply Panu Boonpromsook

Thank you so much. I was stuck with this problems for couple day.
on July 13, 2012 at 8:12 pm | Reply Rick

Awesome. Thank you.
on July 16, 2012 at 1:47 am | Reply karthik

Thank you very much.., u saved lot of time for us.
on July 18, 2012 at 9:51 pm | Reply Sushil Pugalia

Thanks for this nice help.
on August 7, 2012 at 2:22 pm | Reply srikanth kollipara

Thanks alot
on September 5, 2012 at 4:20 am | Reply Thuy

Thanks :)
on September 14, 2012 at 6:11 pm | Reply Olga

I see – not only me broke forehead on this problem because there are comments from 2009 to 2012:)! Thanks & thanks
on December 13, 2012 at 12:00 pm | Reply neeraj

thanks pinal because of you thousands of hrs are saved daily……….great work
on December 22, 2012 at 12:10 pm | Reply nasir

thank u
on December 26, 2012 at 9:31 am | Reply khemarak

very good. thx you
on January 7, 2013 at 6:44 pm | Reply Burak TARHANLI

Thank you, master! This is a great article, like all the others.
on February 13, 2013 at 3:00 am | Reply Kamran Haider Rizvi

Thank You Pinal
on February 14, 2013 at 3:48 am | Reply Calvin

you rock man :o) thank you
on February 25, 2013 at 2:33 pm | Reply quyennb

Thanks alot.
on February 26, 2013 at 8:21 am | Reply fuzzybill74

Thanks a LOT!
on March 3, 2013 at 2:06 pm | Reply rameesh

thanks
on March 20, 2013 at 10:20 pm | Reply sai

Thank you.
on April 11, 2013 at 9:25 pm | Reply Linda

Thank you!
on April 19, 2013 at 11:06 am | Reply AnWar Sadique

thank u very much…
on May 10, 2013 at 5:26 pm | Reply Ahmed

Perfect
on May 11, 2013 at 3:22 am | Reply Bruce

THANK YOU!