This blog post is meant to help distinguish the differences between REORGANIZE and REBUILD when it comes to index fragmentation. If you are serious about learning more of the internals of how indexing works in SQL Server, watch Kimberly Tripp’s (blog | twitter) video called, “Index Internals” here (open link and scroll down to “Index Internals”).
INDEX REORGANIZE vs REBUILD in SQL Server
REORGANIZE – This is the lightweight version. It reorders the leaf pages of the index in a logical order. It is done ONLINE and doesn’t ROLLBACK if interrupted. If for some reason the REORGANIZE operation is interrupted, it can stop at the last operation and continue where it left off. The fill-factor is reset to the previously set fill factor. REORGANIZE is single threaded only and only requires 8 KB of free space to run.
ALTER INDEX [PK_Index_Name] on [MyTable] REORGANIZE;
GO
REBUILD – Creates new indexes and you can define the fill factor. Can be done ONLINE (with SQL Server Enterprise Edition) or OFFLINE. REBUILD is a fully transactions operation. If the operation is interrupted it will have to ROLLBACK the transaction and that can take a while depending how large your indexes are, etc.
ALTER INDEX [PK_Index_Name] ON [MyTable] REBUILD;
GO
For all the specific ALTER commands that you can use with REORGANIZE / REBUILD, visit the MSDN article here.