Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment
up vote 3 down vote accepted

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!

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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