Time to Defrag Your Indexes?

USE tempdb
SET NOCOUNT ON
GO
-- Create a table for testing
IF OBJECT_ID('dbo.TestFrag') IS NOT NULL
	DROP TABLE dbo.TestFrag ;
GO
CREATE TABLE dbo.TestFrag
	(R_ID int NOT NULL IDENTITY (1, 1),	CL_Col int NULL, Filler varchar(8000) NULL) ON [PRIMARY]
GO

-- Populate the table
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (0,REPLICATE('x',625)) ;
GO 10000

-- Set the values in the column in which we will build the Clustered Index
UPDATE dbo.TestFrag SET CL_Col = R_ID

-- Create the clustered index
CREATE CLUSTERED INDEX IX_TestFrag_CL_Col ON dbo.TestFrag
	(CL_Col) WITH (FILLFACTOR = 80) ON [PRIMARY]
GO
-- Create the PK as a non-clustered index
ALTER TABLE dbo.TestFrag ADD CONSTRAINT	PK_TestFrag PRIMARY KEY NONCLUSTERED
	(R_ID) ON [PRIMARY]

GO
-- Look at the fragmentation level before any changes
SELECT index_type_desc, record_count, page_count, avg_fragmentation_in_percent, avg_page_space_used_in_percent, fragment_count, avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.TestFrag'),NULL,NULL,'DETAILED')
WHERE index_level = 0

SET STATISTICS IO ON
SELECT COUNT(*) FROM dbo.TestFrag WHERE CL_Col BETWEEN 499 AND 700 ;
SELECT COUNT(*) FROM dbo.TestFrag WHERE CL_Col = 500 ;
SET STATISTICS IO OFF

-- Step 1

-- Add a single row with a value that is not at the beginning or the end
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (500,REPLICATE('x',625)) ;

-- Look at the fragmentation level after the changes
SELECT index_type_desc, record_count, page_count, avg_fragmentation_in_percent, avg_page_space_used_in_percent, fragment_count, avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.TestFrag'),NULL,NULL,'DETAILED')
WHERE index_level = 0

SET STATISTICS IO ON
SELECT COUNT(*) FROM dbo.TestFrag WHERE CL_Col BETWEEN 499 AND 700 ;
SELECT COUNT(*) FROM dbo.TestFrag WHERE CL_Col = 500 ;
SET STATISTICS IO OFF

-- Step 2

-- Add 10 more rows. Each row added will cause a page split
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (520,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (540,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (560,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (580,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (600,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (620,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (640,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (660,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (680,REPLICATE('x',625)) ;
INSERT INTO dbo.TestFrag ([CL_Col],\[Filler]) VALUES (700,REPLICATE('x',625)) ;

-- Look at the fragmentation level after the changes
SELECT index_type_desc, record_count, page_count, avg_fragmentation_in_percent, avg_page_space_used_in_percent, fragment_count, avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.TestFrag'),NULL,NULL,'DETAILED')
WHERE index_level = 0

SET STATISTICS IO ON
SELECT COUNT(*) FROM dbo.TestFrag WHERE CL_Col BETWEEN 500 AND 700 ;
SELECT COUNT(*) FROM dbo.TestFrag WHERE CL_Col = 500 ;
SET STATISTICS IO OFF