SQL Server Index Rebuild and Index Reorganize

How to Rebuild and Reorganize Indexes in SQL server?

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Index Rebuild : This process drops the existing Index and Recreates the index.

USE DatabaseName;
GO
ALTER INDEX ALL ON dbo.TableName REBUILD
GO


Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE DatabaseName;
GO
ALTER INDEX ALL ON dbo.TableName REORGANIZE
GO