Saving Changes is Not Permitted?

In order to test drive the data modeling features of SS2008, I decided to use the engine for a real-world application.  I’m now attempting to use SQL Server to manage the Bateman household.  My first table in the design is a simple list of chores that we need to make sure get done around the house.

As I was putting together some of the details about how I would use this data, I decided to add a new column in order to specify the frequency (in days) which each chore should ideally be performed.  I was quite surprised to get the following error message when tying to add a nullable column to my existing table.


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. 

Are you kidding me?  It wouldn’t let me add an “Allow Nulls” column?  That just seemed absurd so I did a bit more digging.

Apparently, this is now the default behavior for any of the following changes to a table:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column

In order to prevent this default behavior, you simply need to uncheck a box in the table designer options using the Tools -> Options menu item


Expand the Designers section to display the Table and Database Designers options.


To change this behavior, just uncheck the “Prevent saving changes that require table re-creation” checkbox.

Although this is a bit frustrating, at least it is easy to fix and there is plenty of advice out there.  Here are some other people that found and posted this useful tidbit before me:!98EC8584C8BBED54!849.trak

5 Responses

  1. Thank you for this.

    I have same problem. lol :)

  2. Arigato

  3. Thanks for documenting this!

  4. […] article was published at VN:F [1.7.8_1020]please wait…Rating: 0.0/5 (0 votes cast)VN:F [1.7.8_1020]Rating: 0 (from 0 […]

  5. Many Thanks your post is very helpful

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: