Recalculate row size in SQL Server 2008?

Question

It is possible to recalculate row size in SQL Server 2008? Take this example:

declare @counter int
declare @statement nvarchar(max)
set @counter=0
drop table kua2
create table kua2(id int)
while @counter<307
begin
    set @statement = N'alter table kua2 add id'+CAST(@counter as nvarchar(max))+N' nvarchar(max)'
    exec (@statement)
    set @statement = N'alter table kua2 drop column id'+CAST(@counter as nvarchar(max))
    exec (@statement)
    set @counter=@counter+1
end

alter index all on kua2 rebuild

dbcc cleantable (0,'kua2',0)

alter table kua2 add id0 int
alter table kua2 add id1 int
alter table kua2 add id2 int
alter table kua2 add id3 int

When I add id3 column, I got warning about 8060 bytes, but the table has only 5 int columns, it still count the dropped nvarchar(max) columns in row size.

The only thing that helps is recreate the table. But for several reasons I don't want to do that. Is there any way to tell the SQL Server to recalculate the row size somehow?

Answer 1

This is a bug (albeit a fairly minor one) that still exists in SQL Server 2012 RC0. Report it at connect.microsoft.com

(Thanks Mr Denny).

Answer 2

You are only rebuilding the indexes. You should rebuild the table:

alter table kua2 rebuild

W/o this your table has 307 varchar(max) columns, which mean 7368 bytes if all of them have content. +616 for variable lenght array. +38 bytes in null bitmap. Add to this your 5 fixed size int columns, your record size is above 8060. This is pretty much the expected behavior, this is not a bug. See SQL Server columns under the hood.

Answer 3

You're doing "ALTER INDEX ALL REBUILD" on your KUA2 table, but it has no indexes in the first place. You can't rebuild a heap.

If you create a temporary clustered index:

CREATE CLUSTERED INDEX TempIndex ON Kua2(ID);

you'll force a reorganization of the table storage, including packing the metadata. If you don't need the clustered index (which would be odd -- why would you want a heap?) then you can drop it:

DROP INDEX Kua2.TempIndex;

Whether you drop it or not, you can then add more colummn without worrying about the rowsize limit, and you'll enjoy much better storage efficiency too.

alter table kua2 add id4 int