I have added a column to a table in SQL Server 2005. When I attempt to save it, I get this message:

Saving changes is not permitted. The change 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.

The column I added was nothing special, a nullable nvarchar(11) with no index or constraints. I have never seen this problem before, and have changed the table many times earlier.

Anyone know what's going on, and where I should look for the mentioned option?


Found it.

It was actually an option in SQL Server Management Studio 2008 (which I am using, even though the DB in question is 2005), and not in the DB or server instance as I expected.

The option is exactly as stated in the error message "Prevent saving changes that require the table to be re-created". I found it in Management Studio under Tools -> Options -> Designers, and it is obviously enabled by default. A very strange default indeed!

| improve this answer | |
  • 3
    They added it because this actually results in Management Studio selecting the data in the table into a temp table, dropping the table and recreating it, then reinserting the data back in from the temp table. Not ideal in many situations. – squillman Sep 16 '09 at 18:24
  • Yeah, I can see that, I guess. – Tor Haugen Dec 22 '09 at 23:53

It is probably coming up because you aren't putting the column at the end of the table. Putting a column in the middle of the table causes the table to be dropped and recreated.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.