Programming in MS SQL Server 2012

Rebuild Indexes



Rebuilding an index can be executed online or offline. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

The following example describes to rebuild the IX_Employee_Nonclustered index on the dbo.Employee table.

-- Example 158 --

 

USE EBusiness

GO

 

ALTER INDEX IX_Employee_Nonclustered ON dbo.Employee

REBUILD

GO


Query Output Screen



The following example describes to rebuild ALL indexes on dbo.Employee table.

-- Example 159 --

 

USE EBusiness

GO

 

ALTER INDEX ALL ON dbo.Employee

REBUILD WITH (

              FILLFACTOR = 80,

              SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON

             )

GO


Query Output Screen



* * * * *


Email Your Comment To AUTHOR