Sql-server – Reorganise index vs Rebuild Index in Sql Server Maintenance plan


In the SSW rules to better SQL Server Database there is an example of a full database maintenance plan: SSW. In the example they run both a Reorganize Index and then a Rebuild Index and then Update Statistics. Is there any point to this? I thought Reorganize Index was a fast but less effective version of Rebuild Index? and that an index rebuild would also update the statistics automatically (on the clustered index at least).

Best Solution

The reorganize and rebuild are different things.

Reorganize: it's a defrag for indexes. Takes the existing index(es) and defragments the existing pages. However if the pages are not in a contiguous manner, they stays like before. Only the content of the pages are changing.

Rebuild: actually it drops the index and rebuilds it from scratch. It means that you will get a completely new index, with defragmented and contiguous pages.

Moreover with rebuild you can change partitioning or file groups, but with reorganize you can defrag not only the whole index, but also only one partition of the index.

The update statistics is automatic on clustered indexes, but not on the non-clustered ones.