How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

Question

We are dealing with very large tables in Sql Azure.

Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends.

Metadata operations are quick and we can run them in production at any time.

Looking at a given ALTER TABLE statement, how can I determine if this will be a metadata-only operation or a size-of-data operation?

As far as I know it's not possible to view an ALTER's execution plan?

Answer 1

Generally there are three different ways SQL Server can proceed when it comes to table alteration.

1. Metadata only changes
   examples - dropping a column, changing a not nullable column to nullable one or adding a nullable column to the table.
2. Alteration requires changing the metadata only, but SQL Server needs to scan the table to make sure it confirms to the new definition. example - changing a nullable column to not nullable.
   In this case SQL Server will need to scan all the data rows in the table to make sure that there are no null values stored in that particular column.
3. Alteration requires changing all the rows in the table in addition to the metadata.
   Example - change a fixed-length char column to varchar. In this case SQL Server will need to move the data from fixed to variable length section of the row.

I read about table alteration in the SQL Server Internals book by Dmitri Korotkevitch sometime back and found it very helpful to understand this topic.

Though as suggested by Kin in the last answer, there is nothing better than being able to test it out and assess the impact before going into production with the change. Hope this helps.