Adding/removing columns enough times results in a row size violation - by Adam Machanic

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


6
1
Sign in
to vote
ID 277130 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 5/14/2007 1:56:51 PM
Access Restriction Public

Description

Removing columns from a table is a metadata change, but over time this can add up and produce a nasty and confusing exception when you try to add more columns to a table.  SQL Server will report that your table has exceeded the row size, even when--logically speaking--it is well below the threshold.

Sign in to post a comment.
Posted by Scott Herbert on 3/23/2010 at 7:35 PM
Perhaps some more descriptive error messages would be in order.
Posted by Microsoft on 7/30/2007 at 5:14 PM
Hello Adam,

This behaviour is expected. Dropping a column is a metadata-only change and can leave gaps in column-offsets on the row. When new columns are added to such a table the space left by the dropped columns may or may not be reused for the new column; it depends on the size and type of the old/new columns. Bottom-line is that if you drop/add columns enough times there will be a point at which the max-fixed-size of the row will be exceeded due to the "holes" left behind by the dropped columns.

How to fix this? If your table has a clustered index, then just rebuilding the clustered index will recompact the rows getting rid of the holes left behind by dropped columns. If your table is a heap, then there is no direct way, but you could create/drop a dummy clustered index just to get this above effect. You could use DBCC CleanTable, but it only reclaims space from dropped variable length columns (not fixed length columns). We are looking into the heap issue for a future release.

Thanks for your feedback.

Sameer Verkhedkar
SQL Server Engine
[MSFT]