DeFragmenting Heap Tables.

Ever wanted to know how to deal with fragmentation on tables without any index/primary key, the heap tables. Here is a script that one of my friend forwarded to me when I asked him this question.

BEGIN TRAN

        ALTER TABLE [dbo].[XYX_HeapTable] ADD [c] VARCHAR(1) NULL
        CREATE  CLUSTERED  INDEX [i] ON [dbo].[XYX_HeapTable] ([c]) WITH  FILLFACTOR = 100
        DROP INDEX [dbo].[XYX_HeapTable].[i]
        ALTER TABLE [dbo].[XYX_HeapTable] DROP COLUMN [c]

COMMIT TRAN
Where XYZ_HeapTable is a name of the table which in heap (no primary key nor indexes on it). I wanted to test run this script to see the effectiveness of the above script. So I wrote down the following batch of scripts,

USE Tempdb

GO

CREATE TABLE XYX_HeapTable (
        TrackId INTEGER,
        Fname VARCHAR(200),
        Sname VARCHAR(200)
)

SET NOCOUNT ON

DECLARE @iCount INTEGER
SET @iCount = 0
WHILE @iCount < 10000
BEGIN
        INSERT INTO XYX_HeapTable VALUES (@iCount, '234234234234232432as34234232432dfasdfasdfasdfasdfasdfasdfasdfasdf', 'asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf')

        SET @iCount = @iCount +1
END




DECLARE @ObjectId INTEGER
SELECT @ObjectId = OBJECT_ID('XYX_HeapTable')

DBCC SHOWCONTIG (@ObjectId,0) WITH TABLERESULTS, FAST

GO

BEGIN TRAN

        ALTER TABLE [dbo].[XYX_HeapTable] ADD [c] VARCHAR(1) NULL
        CREATE  CLUSTERED  INDEX [i] ON [dbo].[XYX_HeapTable] ([c]) WITH  FILLFACTOR = 100
        DROP INDEX [dbo].[XYX_HeapTable].[i]
        ALTER TABLE [dbo].[XYX_HeapTable] DROP COLUMN [c]

COMMIT TRAN

GO

DECLARE @ObjectId INTEGER
SELECT @ObjectId = OBJECT_ID('XYX_HeapTable')
DBCC SHOWCONTIG (@ObjectId ,0) WITH TABLERESULTS, FAST

GO

DROP TABLE XYX_HeapTable

Conclusion: I saw good improvement in ScanDensity and ExtentFragmentation after running of the above script, so it works.