SQL Server Interview Question #138

What is the difference between REORGANIZE and REBUILD?

Indexes & Query Performance Senior Advanced

Detailed Explanation

ALTER INDEX ... REORGANIZE performs an online, incremental defragmentation of leaf-level index pages and generally requires less resource at one time. ALTER INDEX ... REBUILD creates a new index structure and can more thoroughly address fragmentation and page density.

Rebuild operations are more resource-intensive and can affect logging, locking, CPU, and I/O. Availability of online rebuild features depends on SQL Server version, edition, and index characteristics.

Maintenance should be workload-driven rather than based only on generic percentage rules.

Code Example

ALTER INDEX IX_Products_CategoryId
ON dbo.Products
REORGANIZE;

ALTER INDEX IX_Products_CategoryId
ON dbo.Products
REBUILD;