SQL SERVER – Difference Between Index Rebuild and Index Reorganize

Note: Frequently asked Question in Interview.

Index Rebuild: In this Indexing process first drops the existing Index and recreates the index.

Example: 

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize :  In this Indexing process Index physically reorganizes the leaf nodes.

Example:
 

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Now Question is that when we can use Rebuild Index and Reorganize Index.

So that when the fragmentation is greater than 40% than we can use the Rebuild option but it's take the more memory resources of Operating System and also Database.

When the fragmentation is between 10% To 40% then we can we the Reorganize indexing.