Rebuilding a heap

Dear 丶 2022-01-05 18:27 217阅读 0赞

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:

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!

转载于:https://www.cnblogs.com/flysun0311/archive/2013/02/25/2932420.html