Annoying SQL Server 2008 behavior

  • Article

In SQL Server management studio when you try to save changes after altering table you might experience an error message that says something like “saving changes not permitted. The changes you have made require 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”

Basically if you make following changes

  • Allow Null setting
  • Re-ordering columns in table
  • Change column data type
  • Add a new column

This will cause a table drop and re-create and by default since “prevent changes that require table re-creation is turned on” which is a good thing as above operations can cause data loss, you won't be able to save your changes.

This can be annoying when you are developing on your workstation, workaround is to use Alter table scripts or turn off the option in SQL management studio

If you are turning off the option in SQL management studio be aware of the risks associated for ex. data loss

Instruction to turn off “Prevent changes that require table re-creation” option

Click on option menu under tools

sqlblog1

Un-check “prevent saving changes that require table re-creation” option

sqlblog2

Following support article has more information on the issue

 

Technorati Tags: SQL Server 2008,Development