A table object that doesn't contain a Clustered Index is called Heap. It does not mean that this table has no indexes. The table may contain several non clustered indexes on it.
I personally dont recommend any kind of Heap table in database. But sometime we find it as for functional support. I do not understand why we create the Heap table? If we do not have any option to primary key we can use the surrogate key.
The problem is when a Heap table is highly fragmented.
This article is trying to identify the Heap table fragmentation issue and try to defragment it.
How to Identify Heap table Fragmentation Percent
DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks2008R2'); SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.tbl_EMPLOYEE'); IF @object_id IS NULL BEGIN PRINT N'Invalid object'; END ELSE BEGIN SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED'); END GO
The third parameter in sys.dm_db_index_physical_stats is for index_id, and we should use zero (0) when the table is a heap. If you use zero and the table is not a heap, you will receive an error. How to Defrag Heap
1. Using the REBUILD option of ALTER TABLE (Only MS SQL 2008 and higher)
ALTER TABLE dbo.tbl_EMPLOYEE REBUILD; GO
2. We will need to select a column to create the clustered index on; keeping in mind this will reorder the entire table by that key. Once the clustered index has been created, immediately drop it.
CREATE CLUSTERED INDEX cluIdx1 ON dbo. tbl_EMPLOYEE(col1); GO DROP INDEX cluIdx1 ON dbo. tbl_EMPLOYEE; GO
3. By manually moving all data to a new temporary table.
CREATE TABLE dbo.tbl_EMPLOYEE_Temp(col1 INT,col2 INT); GO INSERT dbo.tbl_EMPLOYEE_Temp SELECT * FROM dbo.tbl_EMPLOYEE; GO
Next, drop the old table, rename the temporary table to the original name, and then create the original non-clustered indexes.
DROP TABLE dbo.tbl_EMPLOYEE; GO EXEC sp_rename'tbl_EMPLOYEE_Temp','tbl_EMPLOYEE'; GO CREATE NONCLUSTERED INDEX idx1 ON dbo.tbl_EMPLOYEE(col1); GO CREATE NONCLUSTERED INDEX idx2 ON dbo.tbl_EMPLOYEE(col2); GO
Hope you like it.