0

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?

1
  • There is no exhaustive resource providing a matrix. Always test and measure out the impact. Old but still relevant : sqljudo.wordpress.com/2014/03/26/… . Overtime, many operations have become online.
    – Kin Shah
    Mar 30, 2020 at 16:12

1 Answer 1

3

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.

2
  • Not sure how to test though. Our test database is tiny so all ALTERs execute super fast.
    – Clement
    Mar 31, 2020 at 6:31
  • In summary: Are you adding a nullable column, dropping a column or changing a column to be nullable, you're fine. And, when you test DB is too small, feed it some made up data. It will get nice and fat. Just avoid the wafer thing dinner mints.
    – Graham
    Jun 10, 2020 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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