Fixing the Fragmentation of HEAPS
Join the DZone community and get the full member experience.Join For Free
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
To identify whether your heap table is fragmented, we need to either run DBCC SHOWCONTIG (2000 or 2005) or use the DMV sys.dm_db_index_physical_stats (2005 and later)
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.
Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
A Complete Guide to AWS File Handling and How It Is Revolutionizing Cloud Storage
VPN Architecture for Internal Networks
Top 10 Pillars of Zero Trust Networks
Apache Kafka vs. Message Queue: Trade-Offs, Integration, Migration