SQL Management Studio 2008 Error: Saving changes is not permitted

Recently I came accross a blockade after upgrading my SQL Management Studio from 2005 to 2008. All of a sudden if I added a “no-null” option column in a table or inserted a column in the middle of the existing schema I could not save. At first I thought there was some hidden permission I was missing, but after futher research I find out it’s a setting in the Studio itself.

The full error is: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

To get rid of this annoying “feature”, take these steps.

  1. Go to Tools > Options > Designers > Table and Database Designers via the menu
  2. Uncheck the option ‘Prevent saving changes that require table re-creation

Note: This is a safety feature and turning this off opens you up to possible loss of data, so make sure you know what you’re modifying or at least make a backup.

MSSQL Update/Insert Based Upon Existing Record

In the past I learned how to combine one query to correctly update or insert a record based upon if it exists or not. I would only use this if I didn’t already know if it existed because an un-needed select statement would be run and it increases the size of the query string. Example:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)

Thanks to Jeremiah Clark’s Blog, I’ve found an often better way of doing this.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)

The first example will do a table/index scan for both the SELECT and UPDATE statements. The second example will perform one less table/index scan, likely increasing performance.