Rebuild And Reorganization of Indexes

SQL Indexes SQL Server

Rebuild and  Reorganization of Indexes

SQL Server has the ability of maintaining the indexes whenever we makes changes (update, Insert, Delete) in the tables. Over a period of time, the may causes the fragmentation on the table in which  the logical ordering based on the key value pairs does not match with the physical ordering inside the data files. This causes the degradation of the performance of the SQL Query.To solve this problem of fragmentation, we use rebuilding or reorganization of the indexes.In case of Rebuilding, it drop the particular index and again recreate it.It removes fragmentation, reclaims the disk space by compacting the pages based on the specified or existing fill factor setting, and again reorders the index rows in those contiguous pages. We can rebuild all the indexes of the table within a single transaction by specifying the ALL with it.

ALTER INDEX [Indexname] ON [tablename] REBUILD


In case of Reorganization, it defragments the leaf level nodes of indexes by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.  It uses minimal system resources and also compact the index pages

ALTER INDEX [Indexname] ON [tablename] REORGANIZE


Rebuild should be used when the fragmentation index is greater than 30% and reorganization option should be used when the fragmentation index is between 5% and 30%. Rebuilding of an Index can be done online or offline. But to achieve the availability of the index, rebuilding should be done online. Reorganization can be done online.

Related Article


31 thoughts on “Rebuild And Reorganization of Indexes

  1. Pingback: buy anabol
  2. Pingback: roulette
  3. Pingback: keto
  4. Pingback: w88
  5. Pingback: ww88
  6. Pingback: 우리카지노
  7. Pingback: didi promo code
  8. Pingback: 바카라사이트
  9. Pingback: Dream Market Laden
  10. Pingback: Empire Market
  11. Pingback: porno
  12. Pingback: honda qq
  13. Pingback:
  14. Pingback: granice
  15. Pingback: poker 99

Leave a Reply