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

Question

Please, can anyone explain me what is the difference between Index Rebuilding and Index Reorganizing? Thanks in Advance

Answer 1

There are a number of differences. Basically, rebuilding is a total rebuild of an index - it will build a new index, then drop the existing one, whereas reorganising it will simply, well... it will reorganise it.

This blog entry I came across a while back will explain it much better than I can. :)

Answer 2

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 3

"Reorganize index" is a process of cleaning, organizing, and defragmenting of "leaf level" of the B-tree (really, data pages).

Rebuilding of the index is changing the whole B-tree, recreating the index.

It’s recommended that index should be reorganized when index fragmentation is from 10% to 40%; if index fragmentation is great than 40%, it’s better to rebuild it.

Rebuilding of an index takes more resources, produce locks and slowing performance (if you choose to keep table online). So, you need to find right time for that process.

Answer 4

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.

Answer 5

rebuild index rebuild one or more index for a table in the spacified database. reorganised - defragment clustred and secondary indexs of hte spacified table