Index Rebuild vs Index Reorganize. Diffrence beweent Index Rebuild and Index Reorganize


Index Rebuild Index Reorganize
It is offline operation It is online operation
Option is available in all SQL Server 2005 edition Option is available in SQL Server 2005 Enterprise and Developer edition only
Index rebuilds works by re-creating the index internally again and when that has been achieved, it drops the existing index. Index reorganize is the process of physically re-organizing the leaf nodes of the index
Index rebuild need more log space so it is advisable to change the database recovery model Simple or Bulk-logged Reorganize swaps one page with another and thus does not require free space for this operation like rebuild does. Infect, reorganize can free up some pages as it does the reorganize in two phases – compaction and defrag.

A reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation in which the previous and the next extents are physically contiguous.

During the index rebuild process, It will also re-compute index statistics Reorganize on the other hand does not update the statistics
Can be done on any data type column because it is offline operation. Another point to note is that an index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).
If you want to rebuild an index with multiple partitions in an online environment, you need to rebuild the entire index which means rebuilding all the partitions. Regarding partitions of an index, if an index has multiple partitions, then you cannot rebuild a single partition online. You can reorganize a single index partition online
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%
ONLINE option will not keep index available during the rebuilding. ONLINE option will keep index available during the rebuilding.
Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s