Table size increase after data alter

Question

I have noticed when changing the data type of a column, the table size can double with a simple ALTER statement (Reclaiming Space After Column Data Type Change).

Why does SQL Server do this and what causes it?

I have seen posts for how to resolve the issue, how to observe the issue, but nothing about why it is happening and what SQL Server is doing under the hood.

The seemingly correct method is to create a new table, put all the data in the new table with the correct data type, drop the old table, and sp_rename the new table (as seen when you script out the change from using the Designer in SSMS). But it is odd that a straight table alter would not provide a warning or error message when it considers the operation one that "requires table re-creation".

Answer 1

It depends on the nature of the ALTER. A lot of times this is in fact implemented by creating a new column, copying the data across and marking the old column as dropped. Example:

CREATE TABLE T1(X CHAR(10));

INSERT INTO T1 VALUES (REPLICATE('X',10));

ALTER TABLE T1 ALTER COLUMN X CHAR(12);

And then looking at the row in SQL Server internals viewer you can clearly see this

You can also look at the results of the query under Inspecting the physical table structure

the main question is what is happening during a column alter to double the size of a table?

There are several possible things that can contribute.

In the above case the row size was doubled with a single ALTER COLUMN so that would easily explain the overall table size doubling.

Even in the case that the row itself doesn't double in size the fact that the row becomes wider can cause page splits and increase the level of internal fragmentation. This is the case in the article you linked. It goes from having pages 99.8% full to 55% full. Every leaf page needed to be split as there was not sufficient room to accommodate the wider rows so that doubles the number of leaf pages.

Also sometimes previous changes that were implemented as metadata only are deferred and will be written to the row now it is being updated anyway. An example of such a change would be ALTER TABLE T1 ADD Y CHAR(12) NULL this is generally a metadata only change at time of execution and the physical row won't be updated to reflect the additional 12 bytes for this column until next time it needs to be written to.