Rebuilding an Index
Rebuilding an index means dropping an existing index and creating a new one. Any fragmentation that was in the older index ie deleted with the drop and in the new index the logical order matches the physical order.
In SQL Server Index can be rebuilt in two ways
1. Dropping and Re-creating an Index
2. Alter Index or Use the DBCC DBREINDEX statement
Although Dropping and Re-Creating an Index is the slowest way to reduce fragmentation, it can sometimes boosts the performance also. One has to be careful while applying this technique. So, if you need to drop and re-create both clustered and nonclustered indexes then drop the nonclustered indexes first and the clustered index last, and then create clustered index first and the nonclustered indexes last.
On the other hand Alter Index is a more efficient way of reducing fragmentation in comparison with dropping and re-creating an index. In versions prior to SQL Server 2005, DBCC DBREINDEX statement can be used for this purpose. In the later version, ALTER INDEX with REBUILD Clause replaces the DBCC Statement. Both clustered and non-clustered index can be rebuild however the drawback is that one can not change the index definition which is possible incase of re-creating the index.
So we can apply any of the above technique depending on the requirement to rebuild the indexes in the database periodically to reduce the fragmentation.
Reorganising an Index
This is another technique to reduce the index fragmentation where the indexes are reorganised so that the physical order of the index pages matches with the logical order, As a result of which the performance of index scan improves. In verrsions prior to SQL Server 2005, DBCC INDEXDEFRAG statement can be used for this purpose. In the later version, ALTER INDEX with REORGANIZE Clause is used. It doesn’t take a lot of resources and can be done while users are accessing the table that the index exists on, that’s why it is an “online” operation.
So we can apply this technique on all the indexes on all the tables in the database periodically to reduce fragmentation. Since it is an online operation, its better to schedule it during CPU idle time and slow production periods .