THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

2008: Rebuilding a Heap

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!

Published Tuesday, February 26, 2008 11:10 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

joewebb0 said:

Nice tip, Louis. Thanks!

Joe

February 27, 2008 6:57 AM
 

Denis Gobo said:

I second that, nice one

February 27, 2008 7:03 AM
 

AaronBertrand said:

Ooh, that looks like a nice enhancement, good one Louis!

February 28, 2008 7:46 PM
 

Gokul said:

Rebuilding Heap is now very easy with ALTER TABLE. Thanks Louis. Nice Tip.

March 11, 2009 6:25 AM
 

Gokul said:

Space is not released after you delete some rows from a table. Why SP_SPACEUSED doesn't reflect the latest changes?

March 11, 2009 6:28 AM
 

Mark said:

First words out of my mouth "Nice!". Then, I read the comments.... :-)

August 25, 2010 11:02 AM
 

Pasha said:

Good Enhancement.. thanks for sharing

June 17, 2011 12:40 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement