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