DBCC DBREINDEX Vs DBCC INDEXDEFRAG

DBCC DBREINDEX Vs DBCC INDEXDEFRAG

DBCC DBREINDEX 
1. DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table.

2. DBCC DBREINDEX is an offline operation.

3. While this operation is running, the underlying table is unavailable to users of the database.

4. Performing the rebuild requires adequate free space in the data file(s). If not enough free space in the data files, DBCC DBREINDEX may be unable to rebuild the indexes.

5. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. 

DBCC INDEXDEFRAG 

1. DBCC INDEXDEFRAG allows you to rebuild a specific index. 

2. Similar to using DBCC DBREINDEX, you do not have to know about the underlying table structure; however, with DBCC INDEXDEFRAG you cannot rebuild all indexes with a single statement. 

3. You must run DBCC INDEXDEFRAG once for each index you want to defragment. 

4. Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG is an online operation; therefore, the table and indexes are available while the index is being defragmented.

5. Another major difference is that DBCC INDEXDEFRAG can be stopped and restarted without losing any work. This means if you stop DBCC DBREINDEX the entire operation is rolled back, and you must start over.

6. However, if you stop DBCC INDEXDEFRAG it stops instantly and no work is lost, because each unit of work performed by DBCC INDEXDEFRAG occurs as a separate transaction.