SQL Server Management Studio 2008 and table re-creation
“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.”… What? I was just changing the null constraint on a column, why are you talking about dropping a table?
When it hits you the first time, you’re probably confused. The fix itself is easy: Just open Tools / Options and select Designers / Table and Database Designers from the tree, then uncheck “Prevent saving changes that require table re-creation”.
But they didn’t add this warning for nothing. KB956176 describes the phenomenon and the risks involved. There are two major things:
First, when a table gets recreated, that is literally what happens. If you look at the operation in SQL Profiler, you see that a new temporary copy of the table gets created, all the data is transferred by a INSERT INTO … SELECT statement, the original table is dropped and the new one then renamed back to the original name. If you have loads of data, this will take a while. Oh, and you had some indexes? They’ll get recreated and repopulated, too.
Second, if you use SQL Server Change Tracking, deleting and recreating the table wipes out the change tracking information. Depending on how heavily your application relies on CT, the effect may range from a minor nuisance to a total disaster.
When do I need a table rebuild?
Surprisingly often. Although you can add new columns to the end of the table, adding them anywhere else in the table structure (such as between other columns) requires a rebuild. If you change the Identity specification (“autonumbering”) of a field, that warrants a rebuild. Perhaps the most common case is when you change the data type or the null allowance on a column.
So yes, it happens more often than you think.
Why hasn’t this been popping out for years already?
Because older versions of SQL Server Management Studio (and before that, Enterprise Manager) allowed you to save changes that transparently went on to re-create the tables. It was only in SQL Server 2008 when this behavior changed.
And why? Well, before 2008 it was only a slowdown and a (potentially long) service break while the table was rebuilt. With change logging added, the risk of an actual data loss is now tangible. Therefore, a change in policy.
January 29, 2010
· 
Jouni Heikniemi · 
3 Comments
Tags: SQL Server · Posted in: Misc. programming
3 Responses
Marcelo - July 27, 2010
This will make as to use always SQL Server 2005 for our software developing. In design and starting phase of projects, always is needed to change table structures. No mater how much time have you been analyzing and designing. Always receives more information after starting the design. And if it’s not possible to use SQL Management Studio 2008 to make these changes, I will have to use SQL Server 2005 and later implement the model on SQL Server 2008.
Ronald de Heer - September 14, 2011
If you disable the 'Prevent saving changes that require the table re-creation' option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you do not receive the error message anymore and it operates like before
Reyes Oehrlein - January 12, 2021
Outstanding post, you have pointed out some superb details , I besides believe this s a very superb website.
Leave a Reply