The Problem

I generally like using the IDE’s that Microsoft provide for developers. But in this case, I had an annoying problem. When creating my database model I always use Microsoft SQL Server Management Studio. As you all know, during development your tables often change a bit. You create new columns, delete old ones, change the type of a column, define new keys, etc.

In SQL Server 2005 SSMS I don’t remember doing one of the above actions resulted in an error. Now I’m using SQL Server 2008 SSMS and I repeatedly got the error: “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”.


SQL Server 2008 SSMS Save Changes Error

This is weird, what’s it all about?! Of course I asked my friend Google and he pointed me, as usual, in the right direction.

Why?

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 apparently 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 data type 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 the 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.

How do you turn it off?

Menu > Tools > Options > Designers > Uncheck “Prevent Saving changes that require table re-creation”.

SQL Server 2008 SSMS Options

As you can see on the image below, this feature was not available in the 2005 version.

SQL Server 2005 SSMS Options