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. <\/p>\n
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. <\/p>\n
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:<\/p>\n
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)<\/p>\n
Now, check the stats of the table (using the index stats dmv, no less)<\/p>\n
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’) <\/p>\n
This returns:<\/p>\n
index_type_desc fragment_count page_count forwarded_record_count
——————— ——————– ——————– ———————-
HEAP 6 1443 9961<\/p>\n
Now, you can rebuild the heap with the command:<\/p>\n
alter table heapDemo rebuild<\/strong><\/p>\n Check the values now:<\/p>\n select index_type_desc, fragment_count, page_count, forwarded_record_count This shows no that the forwarded_record_count is 0, which is the idea situation for your heap.<\/p>\n index_type_desc fragment_count page_count forwarded_record_count Nice new addition!<\/p>\n","protected":false},"excerpt":{"rendered":" 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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82027","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82027","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82027"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82027\/revisions"}],"predecessor-version":[{"id":82373,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82027\/revisions\/82373"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82027"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
from sys.dm_db_index_physical_stats(db_id(),default,default,default,’DETAILED’)
where object_id = object_id(‘heapDemo’) <\/p>\n
——————— ——————– ——————– ———————-
HEAP 3 1440 0 <\/p>\n