Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

The changes you have made require the following tables to be dropped and re-created

When changing tables in SQL Server Management Studio 2008 you may get the following error: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. I was surprised when I saw this message first but there is very simple solution.

From top menu select Tools and then Options. Select Designer and Table and Database Designers.

MSSQL 2008 table and database designers options

Uncheck the box Prevent saving changes that require table re-creation. Now you can edit your tables without being stopped by re-creation limits.

Update. As mxmissile pointed out in his comment then don't use this on tables with millions of rows.

Posted: Apr 08 2009, 07:24 PM by DigiMortal | with 34 comment(s)
Filed under:

Comments

mxmissile said:

Don't try this on a table that contains millions of records or a live database.

# April 8, 2009 1:01 PM

Felipe Lima said:

Thanks for the info. Certainly this should be only done during development phase in an empty table.

# June 9, 2009 1:56 PM

Sac said:

Thank you very much.

# July 17, 2009 8:16 AM

Leon Han said:

good information

# September 30, 2009 2:27 AM

Andrew said:

Thanks!!!

# March 20, 2010 9:40 AM

Raziq said:

Thanks, I've been facing this problem quite a lot

# April 17, 2010 10:43 AM

Moshe Dubman said:

Thank you. I was just about to drop all my DB tables and try to create the whole DB from scratch ....saved my life!

# June 30, 2010 6:57 AM

Teena said:

thnx alot..this solved my problem

# July 8, 2010 6:52 PM

Ashwini said:

Thank you very much.Solved my problem.

# September 1, 2010 5:54 AM

Blair said:

You saved my from blowing a deadline on an active directory project with 25,000 users! Thank you so much for taking a moment to share.

# September 5, 2010 5:41 PM

Andy said:

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!

# September 13, 2010 5:16 PM

DigiMortal said:

Thanks for question, Andy. There is reason why MS locks this feature by default. Re-creating large tables may take more time than couple of seconds, it may take even hours to make change like this on large tables. The way how Management Studio performs this operation is very costy.

# September 14, 2010 5:18 AM

Andy said:

So it has nothing to do with data loss, purely a consideration because of the intensity of the operation?

# September 20, 2010 12:56 PM

DigiMortal said:

This far I have done this operation without data losses. If tables are large then it may take long hours to recreate them and fill with data again.

# September 20, 2010 4:42 PM

bitcode said:

thanks for the information, that is very helpful one.. cheers!!!

# September 23, 2010 8:30 PM

Igor said:

Thanks man.

# October 14, 2010 11:15 AM

bonaparte said:

I was struggeling for hours, then I have to type the error message, no way to copy it.

Not even a small message to look at the settings.

This post saved my day, thanks

# November 20, 2010 9:13 PM

Arun Prasad said:

Thanks a lot. Saved me my day. I underwent the pain of dropping and re-creating the entire DB (development) a week earlier. Whew!!! An intimation along the "Save Cancelled" dialog box would have been a better option. Seems Microsoft had left its SQL users running helter-skelter by hiding such control info. Also makes an impression that Microsoft's curve bends low towards user-friendliness and goes soaring towards sophistication. While it seems good, not to forget the huge base of confused users left behind.

# December 15, 2010 6:58 AM

fido said:

Thanks.

# December 27, 2010 3:30 AM

nccsbim071 said:

Thank you very much. I was facing this problem since a long time. Your blog post solved it.

# January 22, 2011 6:37 AM

raghu said:

Thanks a lot!!!

# January 25, 2011 5:08 PM

rene said:

thanks! i thought it was a server setting!

# February 18, 2011 2:26 PM

Haitham said:

Thanks alot

# March 25, 2011 5:56 AM

Masu said:

Tnx!

# April 18, 2011 11:18 AM

classicboyir said:

Thanks a lot

# April 20, 2011 6:01 AM

igor said:

Thanks !! :)

# April 20, 2011 6:59 AM

Andrew said:

Thanks a lot!! :D

# April 28, 2011 1:17 PM

alex said:

Thanks a LOT! :D

# June 7, 2011 12:46 AM

Mehrdad said:

ThanKs :)

# August 26, 2011 7:57 AM

Konna said:

good info... thanx

# September 1, 2011 11:29 PM

SS said:

Thank you so much!!!saved my time..:)

# November 12, 2011 2:20 PM

Tommy said:

Thanks... hate MS so much.. forced to work with this terrible DBMS for my current project.

# January 11, 2012 7:28 AM

John said:

Thanks!!

# January 26, 2012 4:35 PM

9oclick said:

thanks, man!

# April 4, 2012 3:45 AM