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

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?

migrated from stackoverflow.com Dec 16 '11 at 13:25

This question came from our site for professional and enthusiast programmers.

  • It is a warning only (not an error), if you now that it's fine for you just do not worry about it. – Michał Powaga Dec 16 '11 at 10:53
  • @Michael - These dropped columns will still bloat the size of the NULL_BITMAP and if a row has a variable length section will still consume space in the column offset array. – Martin Smith Dec 16 '11 at 11:40

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


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.

  • This worked for me. My row length was only 71 bytes (2 integers, 2 dates), but SQL insisted the row exceed 8060. Truncating and performing a rebuild cleared the erroneous internal row size calculation. – James Moberg Dec 9 '15 at 0:46

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:


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.