SQL Server 2008 Prevent Saving Changes Setting

SQL Server 2008 has a setting that PREVENTS saving certain table schema changes (such as changing column order) – and this setting is ENABLED by default.

When I first encountered the effects of this setting, I was attempting to rearrange the column order of a table. Using the GUI, I went to the table design view and moved one column in front of another.

When I hit ‘Save’, I received this message:

When a table’s schema is changed, the process that SQL Server performs drops the table and recreates it. The new setting that prevents this is called ‘Prevent saving changes that require table re-creation‘, and it’s easy enough to turn off. I’m sure it has been put in place as a safeguard to prevent unintended table changes. To turn off the setting, go to (on the menu bar) Tools/Options/Designers/Table and Database Designers, then under the Table Options section, uncheck Prevent saving changes….

Schema changes will not be blocked.  You’re welcome.