SQL Server: What is the difference between Index Rebuilding and Index Reorganizing?

Question

What is the difference between Index Rebuilding and Index Reorganizing?

Answer 1

Think about how the index is implemented. It's generally some kind of tree, like a B+ Tree or B- Tree. The index itself is created by looking at the keys in the data, and building the tree so the table can be searched efficiently.

When you reorganize the index, you go through the existing index, cleaning up blocks for deleted records etc. This could be done (and is in some databases) when you make a deletion, but that imposes some performance penalty. instead, you do it separately in order to do it more or less batch mode.

When you rebuild the index, you delete the existing tree and read all the records, building a new tree directly from the data. That gives you a new, and hopefully optimized, tree that may be better than the results of reorganizing the table; it also lets you regenerate the tree if it somehow has been corrupted.

Answer 2

REBUILD locks the table for the whole operation period (which may be hours and days if the table is large).

REORGANIZE doesn't lock the table.

Well. actually, it places some temporary locks on the pages it works with right now, but they are removed as soon as the operation is complete (which is fractions of second for any given lock).

As @Andomar noted, there is an option to REBUILD an index online, which creates the new index, and when the operation is complete, just replaces the old index with the new one.

This of course means you should have enough space to keep both the old and the new copy of the index.

REBUILD is also a DML operation which changes the system tables, affects statistics, enables disabled indexes etc.

REORGANIZE is a pure cleanup operation which leaves all system state as is.