I was working with SQL Server 2008 for quite some time and noticed a strange behavior (which is not in previous versions of SQL Server) when I try to save a table in Management Studio that requires table to be dropped and recreated (e.g. Try to ADD a new NOT NULL column to the table which is having data) .I get the following warning message
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.
I though it is bug with SQL Server, but later cam to know it is the expected behavior and can be turned of by unchecking "Prevent saving changes that require table re-creation" in the Designer properties. Please refer to the below screen shot
