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?
- 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.
CREATE CLUSTERED INDEX cl_temp ON dbo.TestTable(col1); GO DROP INDEX cl_temp ON dbo.TestTable; GO
- 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.
CREATE TABLE dbo.TestTable1(col1 INT,col2 INT); GO INSERT dbo.TestTable1 SELECT * FROM dbo.TestTable; GO SELECT * FROM TestTable1 DROP TABLE dbo.TestTable; GO EXEC sp_rename 'TestTable1','TestTable'; GO CREATE NONCLUSTERED INDEX idx1 ON dbo.TestTable(col1); GO CREATE NONCLUSTERED INDEX idx2 ON dbo.TestTable(col2); GO
- 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.
ALTER TABLE dbo.TestTable REBUILD; GO
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.