Drop and Recreate the Index
Some of the disadvantages of dropping and recreating an index with either DROP INDEX and CREATE INDEX or ALTER TABLE includes the disappearance of the index while you are dropping and recreating it. As the index is dropped and recreated, it is no longer available for queries and query performance may suffer dramatically until you can rebuild the index. Another disadvantage of dropping and recreating an index is the potential to cause blocking as all requests to the index are blocked until the index is rebuilt. This technique can also face blocking by other processes, as the process of dropping the index cannot take place while other processes are using the index. Another major disadvantage of this technique is that rebuilding a cluster index with DROP INDEX and CREATE INDEX has the effect of rebuilding all of the nonclustered indexes twice. Once as the clustered index is dropped and the nonclustered index row pointers are pointed to the data heap and again as the clustered index is rebuilt and the nonclustered index row pointers are pointed back to the clustered index row locations.
Dropping and rebuilding an index does have the advantage of completely rebuilding an index which does reorders the index pages, compacting the pages, and dropping any unneeded pages. You may need to consider dropping and rebuilding indexes that show high levels of both internal and external fragmentation to get those indexes back to where they should be.
Recreate the Index With the DROP_EXISTING Clause
To avoid the processing it takes to rebuild the nonclustered indexes twice on a table that you rebuild a clustered index, you can use the CREATE INDEX with DROP_EXISTING clause. This clause will keep the clustered index key values, avoiding the need to rebuild the nonclustered indexes twice. Like the regular DROP INDEX and CREATE INDEX technique this technique can cause/face blocking problems and index disappearance problems. Another disadvantage is that using the CREATE INDEX with DROP_EXISTING technique also forces you to find and repair each index on the table separately.
Besides the advantages associated with the regular drop and recreate technique and the advantage of not having to rebuild nonclustered indexes twice using the CREATE INDEX with DROP_EXISTING clause can be used for indexes with constraints provided that the index definition exactly matches the requirements of the constraints.
Execute DBCC DBREINDEX
DBCC DBREINDEX is similar to CREATE INDEX with DROP_EXISTING, but it will rebuild the index physically allowing SQL Server to assign new pages to the index and reduce both internal and external fragmentation. DBCC DBREINDEX also has the ability to recreate indexes with constraints dynamically, unlike CREATE INDEX with DROP_EXISTING.
The disadvantages of DBCC DBREINDEX is that it faces the problem of causing/facing blocking and DBCC DBREINDEX is executed within a transaction so if it is stopped before completion, you lose all the defragmentation that had been performed.
Execute DBCC INDEXDEFRAG
DBCC INDEXDEFRAG (available in SQL Server 2000) reduces external fragmentation by rearranging the existing leaf pages of an index to the logical order of the index key and internal fragmentation by compacting the rows within index pages then discarding unneeded pages. It does not face the blocking problems of the other techniques but the results of DBCC INDEXDEFRAG are not as complete as the other techniques. This is because DBCC INDEXDEFRAG skips locked pages due to its dynamic nature and does not use any new pages to reorder the index. You may also discover that the time needed by DBCC INDEXDEFRAG is longer than recreating an index if the amount of fragmentation is large. DBCC INDEXDEFRAG does have the advantage over the other techniques due to its ability to work on defragmenting an index while other processes are accessing the index, eliminating the blocking problems of the other techniques.
Thanks
J. Kusch
