"Prevent saving changes that require the table to be re-created" negative effects

Tools --> Options --> Designers node --> Uncheck " Prevent saving changes that require table recreation ".

The table is only dropped and re-created in cases where that's the only way SQL Server's Management Studio has been programmed to know how to do it.

There are certainly cases where it will do that when it doesn't need to, but there will also be cases where edits you make in Management Studio will not drop and re-create because it doesn't have to.

The problem is that enumerating all of the cases and determining which side of the line they fall on will be quite tedious.

This is why I like to use ALTER TABLE in a query window, instead of visual designers that hide what they're doing (and quite frankly have bugs) - I know exactly what is going to happen, and I can prepare for cases where the only possibility is to drop and re-create the table (which is some number less than how often SSMS will do that to you).

Reference - Turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server 2008 to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the "Symptoms" section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore,Microsoft recommend that you do not work around this problem by turning off the option.