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: