Hi Friends,

I see a common practice with DBAs: If you have to rebuild a HEAP, you will create a clustered index and drop it – this two phase process does the rebuild job, but is expensive. SQL Server supports ALTER TABLE REBUILD command to rebuild a heap.

Creating a dropping a clustered index, the old technique, is expensive since it has to update all the non-clustered indexes with the correct pointer, twice! When you create a clustered index, it updates all the non-clustered indexes with a pointer to the clustering key. When you drop the clustered index, it updates all non-clustered indexes with a pointer to the RID (HEAP). So in this technique, non-clustered indexes are rebuilt twice.

Instead, use SQL Server ALTER TABLE REBUILD command and the non-clustered indexes are rebuilt only once.

Here is a quick example to show the performance difference:

I create a table and a few non-clustered indexes:

use AdventureWorks2012
go

--drop table person.persontemp

select * into Person.PersonTemp
from Person.Person

CREATE NONCLUSTERED INDEX 
[IX_Person_LastName_FirstName_MiddleName] ON [Person].[PersonTemp] ( [LastName] ASC, [FirstName] ASC, [MiddleName] ASC ) CREATE NONCLUSTERED INDEX [IX_EP] ON [Person].[PersonTemp] ( [EmailPromotion] ASC ) CREATE NONCLUSTERED INDEX [IX_PT] ON [Person].[PersonTemp] ( [PersonType] ASC ) Now, I will turn ON STATISTICS TIME and record the execution times for CREATE/DROP Clustered Index technique & for the ALTER TABLE REBUILD command. set statistics time on CREATE CLUSTERED INDEX [IX_BEID] ON [Person].[PersonTemp] ( [BusinessEntityID] ASC ) drop index [IX_BEID] on [Person].[PersonTemp] Now, let’s see the execution times for ALTER TABLE REBUILD command: set statistics time on ALTER TABLE [Person].[PersonTemp] REBUILD

 

SQL Server ALTER TABLE REBUILD_2

The difference in execution times is very evident. Especially, note the CPU time in both cases.

ALTER TABLE REBUILD was introduced in SQL Server 2008 (if I am not wrong) and still not known to many, so consider using it in case you have to rebuild a heap, rather than using the old technique.

Shall you have a HEAP? Why not a clustered index? Why do you need a HEAP? Etc, etc, etc – This is completely another story!

But why do you want to rebuild a HEAP?