4

I'm aware that there are some circumstances where making certain changes to a table (for example, changing the type of a column) through the GUI of SSMS will cause the table to be dropped and re-created.

My question is, does a complete definition exist of exactly what changes cause this behaviour? And can this behaviour always be avoided by running an ALTER TABLE instead of using the SSMS GUI (and if not, which can be avoided and which cannot)?

1
1

I doubt Microsoft has anywhere published the logic that is used to determine how SSMS will script a table change. It should be noted that the way SSMS has handled these has evolved over the years - I swear in earlier versions every single change would drop & re-create. Now it definitely tries to make much targeted adjustments.

I can't think of many instances where the only option is to DROP and CREATE. There are many scenarios where it would be the easiest for a human or SSMS to script, but most any situation can be solved by judicious uses of ALTER or dropping and re-creating constraints only. The one absolute exception to this is changing the order of columns - that does require dropping and re-creating the table.

One way to always be sure of what your change is going to do is, in the table designer, once you've made your changes, but before you've saved them, go to the "Table Designer" menu, and select "Generate Change Script." This will tell you exactly what SSMS is going to execute and can allow you to decide before you hit "Save" whether or not that's how you want it done.

0

Things that will cause a DROP and CREATE, aside from your example of changing a data type, usually involve changes to the Primary Key / clustering key. Moving columns will do it, as will changing collation on text-based columns.

I'm not sure what you're trying to avoid, per se.

An ALTER may seem more elegant in code, but it might also be doing a whole lot of work in the background too, running the risk of truncating or failing, so dropping and creating is considered better.

I think their rule of thumb is that in-place data conversions are too risky, especially with tables that already contain data, so it makes more sense to make a new one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.