Labels

Wednesday, 23 December 2015

SQL Server - Difference between Index Rebuild and Index Reorganize

The below are the key differences between Index Rebuild and Reorganize .

S.No

Index Rebuild

Index Reorganize

1


Index Rebuild drops the existing Index and Recreates the index from scratch.

Index Reorganize physically reorganizes
the leaf nodes of the index.

2


Rebuild the Index when an index is over 30% fragmented.

Reorganize the Index when an index is
between 10% and 30% fragmented

3




Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development
editions.

Always prefer to do Reorganize the Index


4






T-SQL for Rebuilding all Indexes of a
particular table.
USE AdventureWorks;
GO
ALTER INDEX ALL ON HumanResources.Employee REBUILD
GO

T-SQL for Reorganize all Indexes of a
particular table.
USE AdventureWorks;
GO
ALTER INDEX ALL ON
HumanResources.Employee
REORGANIZE
GO

No comments:

Post a Comment