Table rebuild in SQL Server

As a DBA, we spend a lot of time investigating and maintaining the database performance parameters like new index creation, index rebuild on basis of fragmentation level, statistics update etc.  but what about a HEAP table, how will we handle the fragmentation of a HEAP?

Method 01:

  • Create and drop a clustered index on the HEAP table.
  • This is an expensive method since it rebuilds all the NON CLUSTERED indexes present in that table and points the data link to new clustered index key instead of RID at the first place, then when you drop the clustered index, it has to rebuild all the NON CLUSTERED indexes again to map the data link back to RID from clustered index. In this case all indexes in the table gets rebuilt twice.

Method 02:

    • Create another table with a different name with same schema and structure of original HEAP table then have the records inserted to the new table from original, then drop the original table and rename the new table. Make sure you have generated all the NON CLUSTERED index scripts before dropping the original table and have them excuted on the new table.
    • This is also an expensive method since a whole new table has to be created and dump over the data freshly.

Method 03:

    • REBUILD the whole table, this is an optimal way to rebuild a HEAP and all the associated indexes. This command can also rebuild a table that has clustered index and associated NON CLUSTERED indexes in it.
    • This feature was introduced in SQL Server 2008 and available in all latest versions.

It’s an ongoing debate that why and when do we really need a HEAP table in our database? Why not create a clustered index?

I would personally recommend to have a clustered index on each table.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: