What happens when I drop a column in SQL Server

Question

If my understanding is right then the number of rows that are stored on one page in SQL Server is determined by the number of columns in the table and their datatypes. One I/O operation can read one page, so the more rows fits into one page, the more rows can be returned by one I/O operation, and your queries run faster.

I wonder what happens when you drop a column? Does SQL Server go back to the memory and "re-stores" the data, i.e what if I drop enough columns for more data to fit on one page? And if SQL Server does not do that automatically, can I force the process?

I'm removing a lot of text columns and a few IDs on the heavily used table, and I hoping that the I/O will improve after I drop the columns.

Answer 1

Dropping a column is a logical operation, not physical. No data gets modified. The column metadata gets marked as 'deleted' and will be ignored. The record size is unchanged. Read SQL Server table columns under the hood for more details explanation and clear examples demonstrating my claim.

As Stefan said, you have to rebuild the table (heap or clustered index) to 'reclaim' the space.

I'm removing a lot of text columns and a few IDs on the heavily used table, and I hoping that the I/O will improve after I drop the columns.

Use indexes to reduce IO. For unindexable ad-hoc analytical workloads, use columnstores. Read How to analyse SQL Server performance.