Is ALTER TABLE DROP COLUMN SQL Server really a metadata-only operation?
I've found several sources that state ALTER TABLE ... DROP COLUMN is a metadata-only operation. Source How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying non-clustered indexes and clustered index/heap? In addition, why do Microsoft Docs imply that it is a fully logged operation? The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. Run these ALTER TABLE statements with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows. As a secondary question: how does the engine keep track of dropped columns if the data isn't removed from the underlying pages?
There are certain circumstances where dropping a column can be a meta-data-only operation. The column definitions for any given table are not included in each and every page where rows are stored, column definitions are only stored in the database metadata, including sys.sysrowsets, sys.sysrscols, etc.
When dropping a column that is not referenced by any other object, the storage engine simply marks the column definition as no longer present by deleting the pertinent details from various system tables. The action of deleting the meta-data invalidates the procedure cache, necessitating a recompile whenever a query subsequently references that table. Since the recompile only returns columns that currently exist in the table, the column details for the dropped column are never even asked for; the storage engine skips the bytes stored in each page for that column as if the column no longer exists.
When a subsequent DML operation occurs against the table, the pages that are affected are re-written without the data for the dropped column. If you rebuild a clustered index or a heap, all the bytes for the dropped column are naturally not written back to the page on disk. This effectively spreads the load of dropping the column over time, making it less noticeable.
There are circumstances where you cannot drop a column, such as when the column is included in an index, or when you've manually created a statistics object for the column. I wrote a blog post showing the error that is presented when attempting to alter a column with a manually created statistics object. The same semantics apply when dropping a column - if the column is referenced by any other object, it cannot simply be dropped. The referencing object must be altered first, then the column can be dropped.
This is fairly easy to show by looking at the contents of the transaction log after dropping a column. The code below creates a table with a single 8,000 long char column. It adds a row, then drops it, and displays the contents of the transaction log applicable to the drop operation. The log records show modifications to various system tables where the table and column definitions are stored. If the column data was actually being deleted from the pages allocated to the table, you'd see log records recording the actual page data; there are no such records.
The first set of outputs shows the log as a result of the DDL statement dropping the column. The second set of output shows the log after running the DML statement where we update the rid column. In the second result set, we see log records indicating a delete against dbo.DropColumnTest, followed by an insert into dbo.DropColumnTest. Each Log Record Length is 8116, indicating the actual page was updated.
As you can see from the output of the fn_dblog command in the test above, the entire operation is fully logged. This goes for simple recovery, as well as full recovery. The terminology "fully logged" maybe misinterpreted as the data modification is not logged. This is not what happens - the modification is logged, and can be fully rolled back. The log is simply only recording the pages that were touched, and since none of the table's data-pages were logged by the DDL operation, both the DROP COLUMN, and any rollback that might occur will happen extremely quickly, regardless of the size of the table.
For science, the following code will dump the data pages for the table included in the code above, using DBCC PAGE, style "3". Style "3" indicates we want the page header plus detailed per-row interpretation. The code uses a cursor to display the details for every page in the table, so you may want to make sure you don't run this on a large table.Looking at the output for the first page from my demo (after the column is dropped, but before the column is updated), I see this: