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.
