Which changes to a table in SSMS cause a DROP/CREATE and can this always be avoided with TSQL? 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)?
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 &amp; 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.