Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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
3  
This is a bug (albeit a fairly minor one) that still exists in SQL Server 2012 RC0. Report it at connect.microsoft.com –  Paul White Dec 16 '11 at 13:18
    
@SQLKiwi you should probably make that an answer, as that is the answer. –  mrdenny Dec 16 '11 at 18:25
    
@SQLKiwi - It's apparently not a bug. You need to rebuild the table in addition to the indexes. –  Nick Chammas Mar 14 '12 at 15:52

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer
    
Welcome. :) I hate the minimum comment size requirement. –  mrdenny Dec 16 '11 at 19:41

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.

share|improve this answer
    
I'm not so sure...the DBCC CLEANTABLE documentation has "...DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. The command does not reclaim space after a fixed-length column is dropped." This is the 'minor' bug I was referring to - no argument with the fact that rebuilding the table completely (using REBUILD, or creating/dropping a clustered index) works around the issue. –  Paul White Mar 15 '12 at 6:12

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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