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?

Data Platform Virtual Summit 2020

Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.

SQLMaestros YouTube | SQLMaestros Bulletin | SQLMaestros Twitter