Saturday, December 10, 2011

Difference Between Index Rebuild and Index Reorganize

Difference between Index Rebuild Vs Index Reorganize with example T-SQL.

1.    Index Rebuild drops the existing Index and Recreates the index from scratch.
2.    Index Reorganize physically reorganizes the leaf nodes of the index.
3.    Rebuild the Index when an index is over 30% fragmented.
4.    Reorganize the Index when an index is between 10% and 30% fragmented.
5.    If fragmentation is below 10%, no action required.
Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions.
6.    Always prefer to do Reorganize the Index.

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
Reorganize and Rebuild Indexes. Index Rebuild versus Reorganize. What's the difference. Reorganise index vs Rebuild Index in Sql Server Maintenance plan. Script to appropriate rebuild/reorganize database indexes. Difference Between Index Rebuild and Index Reorganize Explained. Fragmented Index Rebuild or Reorganize. Rebuild or Reorganize Indexes. Rebuild and Reorganize Fragmented Indexes. ALTER INDEX. REBUILD V/S INDEX REORGANIZE. in sql server 2012. in sql server 2008. in sql server 2005. in sql server 2000. How to rebuild the master database. Rebuilding the Master Database in sql server 2012. rebuild master database in sql server 2008 R2. How to rebuild your master database. The Easiest Way To Rebuild The master Database.

2 comments:

Hi,

As per the Books online says ->the rebuild index operation is introduced in sql server 2005 enterprise edition only and in SQL server 2008 the feauters available is Enterprise,Developer and Enterprise evualtion edition only.

Also in SQL server 2008 what ever may be the recovery model of the database the Rebuild index activity is fully logged.

Thanks,
Rama Udaya.K
ramaudaya.blogspot.com

Post a Comment