Menu
Open SQL Server Management Studio (SSMS). On the Tools menu, click Options. In the navigation pane of the Options window, click Designers. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.
We can use COMMIT command to make the changes, made in a current transaction, permanently recorded in MySQL database. Suppose if we run some DML statements and it updates some data objects, then COMMIT command will record these updates permanently in the database.
So to do that go to SQL Server and within Tools select Options. Now in the option window expand Designers and under that "Table and Database Designers" and uncheck the check box "Prevent saving changes that require table re-creation" then click OK.
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).
Are there any negative effects / possible drawbacks of doing this?
Sure. If you can script the change yourself without rebuilding the whole table, that's better - consider the case where the table is 10TB, and the database is heavily logged (think sync AG, change tracking, replication, poorly-written triggers), and the table is highly accessed - that's a potential recipe for disaster. If your change is something where you can apply an ONLINE hint or add a column and copy the data over in batches instead of all-or-nothing the GUI will do, this is better.
Does the table actually get dropped and recreated automatically when this box is unchecked?
It might. There is a laundry list of scenarios and the outcome is dependent on the version of SSMS, the version of SQL Server, and sometimes the edition. You can check by checking the box and trying to apply the change on a meaningless copy of the database first, but using actual ALTER TABLE scripts instead of the pointy-clicky GUI is the way to go IMHO.
If so, is the table copy a 100% exact replica of the source table?
Yes, if SSMS has to rebuild the table, it will be a 100% exact replica after it's done (except for the change of course), but that could be next Wednesday. The process creates a new version of the table, copies all the data to it, then drop the old table and renames the new one.
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.
SQL Server drops and recreates the tables only if you:
Using ALTER is safer, as in case the metadata is lost while you re-create the table, your data will be lost.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With