We’ve recently upgraded our development laptops with SQL 2008 from SQL 2005. Today I was trying to create a new database for a client within the management studio and every time I decided, for example, to change a column that was defined nullable to not nullable and tried saving my design, which in turn would save the table changes, it gave me the following error:
Now, I understand the need for potentially re-creating the table if for example you had an existing table where a column was nullable and it contained data then you wanted to make it not nullable (without a default value) it would correctly so moan. But you would think that the Microsoft devs would build a system intuitive enough to know that where there isn’t any data in the table yet, because you’re designing the schema, it could automatically drop and re-create the table and any associated relationships.
Needless to say, to prevent this from happening to you – go into the Tools -> Options and click on the Designers item in the tree menu on the left and make sure “Prevent saving changes that require table re-creation” is not ticked as below:
Hope that helps someone else too!