Posted by: Denny Cherry
Config, SQL, SQL Server 2008, SSMS
When editing the table schema in the SQL Server 2008 UI and the required change requires that the table needs to be dropped and recreated by default the UI will not let you make the change.
The kinds of changes that would require that the table be dropped and recreated would be inserting a column in the middle of the table, or changing the size of a column.
This setting can be overwritten by clicking on the Tools drop down menu and selecting Options. Under the Designers menu option, select the “Table and Database Designers” menu option and uncheck the “Prevent saving changes that require table re-creation” option. After clicking OK you will be able to save the changes.
I recommend that you leave this setting enabled normally so that you don’t make table changes which require the object to be dropped by accident on production systems.