SQL Server ALTER COLUMN calculates table width incorrectly

SQL Server 2012

I recently discovered a bug/feature of SQL Server that drove me crazy until a colleague found an article that explained everything. Before I reference the article, read through the following...

Consider this table create statement...
CREATE TABLE WideTable (Col1 int, Col2 char(3000), Col3 char(3000));

Now change the width of Col2 to char(4000) to make the total width = 7004 bytes ...
ALTER TABLE WideTable ALTER COLUMN Col2 char(4000)

You get the error
Msg 1701, Level 16, State 1, Line 2
Creating or altering table 'WideTable' failed because the minimum row size would be 10011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Where does SQL Server get 10011 bytes from? This is a perfectly legal change isn't it? Let's prove it...
CREATE TABLE WideTable2 (Col1 int, Col2 char(4000), Col3 char(3000));

Apparently SQL Server can't do math. That's a little scary. Here's the story...

Let's start off by running a handy query - you might want to save this one.

SELECT  c.name AS column_name, column_id, max_inrow_length,
      pc.system_type_id, leaf_offset
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p
        ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('WideTable');
GO

This query shows the results below.
column_name column_id   max_inrow_length system_type_id leaf_offset
----------- ----------- ---------------- -------------- -----------
Col1        1           4                56             4
Col2        2           3000             175            8
Col3        3           3000             175            3008

Now you can see that there is a four byte overhead before Col1, Col2 starts at offset 8, and Col3 starts at offset 3008. So if we added another column it would have an offset of 6008. A new column with a width of more than about 2050 bytes would exceed the 8060 maximum allowable table row size. Let's try reducing the width of Col2 to 2500 bytes and see what happens.

ALTER TABLE WideTable ALTER COLUMN Col2 char(2500) 

This works. Let's run that query again...
column_name column_id   max_inrow_length system_type_id leaf_offset
----------- ----------- ---------------- -------------- -----------
Col1        1           4                56             4
Col3        3           3000             175            3008
Col2        2           2500             175            8

Look at the column sequence. It changed. It looks as though SQL Server is adding the altered column to the end of the column list and then removing it from the middle. In between those events it looks like there is code that says something like "If the column width is increasing then check that the sum of the columns does not exceed 8060." But it's including the old AND the new widths of the altered column. This check is not performed if the column width is not increasing.

Looks like a bug, smells like a bug, walks like a bug.

The article that helped me on this issue is http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/alter-table-will-not-reclaim-space.aspx. It talks about a slightly different issue but is very relevant to this problem.

The solution is obviously to make a structure change that forces the table to be rebuilt. There's a discussion of this at the end of the referenced blog entry.