Error when modifying table from SQL Server Management Studio in SQL Server 2008
In SQL Server 2008, when you change table from the SQL Server Management Studio, if that change needs the table to be dropped and re-created, it will return and error and the change will not be implemented and the below error will be shown :
This will effect operations like changing an int column property to identity and changing a varchar column to int etc.. In case of table recreation, it can take lot of time if there are a lot of records in the tables.
However, there is an option to allow these changes to occur.
Select Options from the Tools menu. In that dialog go to the Designers option and de-select the highlighted option. By default this option is selected. This will allow the changes for changes in the table which require a drop and recreate.