In 2005, rebuilding a table that was a heap (no clustered index) wasn’t easy. You could copy it to a different table, or you could add a clustered index and then drop it.
In 2008, this is a far easier thing to do. They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered index for a clustered table, but for a HEAP, it is the only way to go.
In the following code sample, I create a heap, load it will “little data”, then expand every row to much larger values. The result is a little bit of fragmentation, but more than that, tons of forwarding pointers (when a row won’t fit on the same heap page, it gets moved to a different page, but the pointer to the row does not change in the indexes.) Rebuilding the heap is now really simple:
create table heapDemo
(
value varchar(1000)
)
GO
set nocount on
insert into heapDemo
select ‘hi’
go 10000
–Expand the values to 500 times the size they were
update heapDemo
set value = replicate(‘hi’,500)
Now, check the stats of the table (using the index stats dmv, no less)
select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,’DETAILED’)
where object_id = object_id(‘heapDemo’)
This returns:
index_type_desc fragment_count page_count forwarded_record_count
——————— ——————– ——————– ———————-
HEAP 6 1443 9961
Now, you can rebuild the heap with the command:
alter table heapDemo rebuild
Check the values now:
select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,’DETAILED’)
where object_id = object_id(‘heapDemo’)
This shows no that the forwarded_record_count is 0, which is the idea situation for your heap.
index_type_desc fragment_count page_count forwarded_record_count
——————— ——————– ——————– ———————-
HEAP 3 1440 0
Nice new addition!
Load comments