Index Fragmentation - SQL Server Query Performance Tuning - page 250

FROM Nums
WHERE n < 21
)
INSERT INTO dbo.Test1
(C1, C2, C3, c4)
SELECT n,
n,
n,
'a'
FROM Nums ;
WITH Nums
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Nums
WHERE n < 21
)
INSERT INTO dbo.Test1
(C1, C2, C3, c4)
SELECT 41 - n,
n,
n,
'a'
FROM Nums;
If you take a look at the current fragmentation, you can see that it is both internally and externally fragmented
(Figure 13-15 ).
Figure 13-15. Internal and external fragmentation
You can defragment the clustered index (or the table) by using the ALTER INDEX REBUILD statement.
ALTER INDEX i1 ON dbo.Test1 REBUILD;
Figure 13-16 shows the resultant output of the standard SELECT statement against sys.dm_db_index_physical_stats .
Figure 13-16. Fragmentation resolved by ALTER INDEX REBUILD