1

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".

2
  • 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 though. Aug 9, 2016 at 16:52
  • I know there is some variability between moving from fixed to variable, longer to shorter byte storage, nullable to not nullable, etc and vice versa. But the main question is what is happening during a column alter to double the size of a table?
    – dfundako
    Aug 9, 2016 at 16:58

1 Answer 1

4

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

enter image description here

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

enter image description here

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.

enter image description here

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.

5
  • Very cool row breakdown tool. What do you use? I usually go look straight at the page through DBCC. The MVCE is in the link in the OP that was just published on SQL Server Central using a sample set of data and table from AdventureWorks2014. The leaf offset in your example I can see is 10 bytes of NULL since it was dropped, but in the example through the link the offset only changes by 4.
    – dfundako
    Aug 9, 2016 at 17:22
  • @dfundako - It's internalsviewer.codeplex.com but unfortunately is no longer maintained and only works with old versions of SSMS. Will take a look at the link... Aug 9, 2016 at 17:23
  • Ahhhh. So this is something that could be potentially avoided if there was a sufficient amount of space given by a fill factor to accommodate the additional bytes from the data type change? If the page was 80% full instead of 99%, it would not have to page split?
    – dfundako
    Aug 9, 2016 at 17:35
  • @dfundako - yes. Aug 9, 2016 at 17:36
  • 2
    You're a gentleman and a scholar. Thanks for the sagely wisdom.
    – dfundako
    Aug 9, 2016 at 17:37

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.