What's the difference between INDEXDEFRAG and DBREINDEX with MSSQL

DBCC INDEXDEFRAG

  1. DBCC INDEXDEFRAG is an online operation. It can be used without locking out users.
  2. DBCC INDEXDEFRAG has no long-term locks that block running queries or updates. Only the local pages are locked.
  3. DBCC INDEXDEFRAG can be used at any time, there are no restrictions.
  4. If low amount of fragmentation, then it's lot faster than DBCC DBREINDEX.
  5. If high amount of fragmentation, then it's lot slower than DBCC DBREINDEX.
  6. DBCC INDEXDEFRAG compacts the pages of an index and empty pages are removed.
  7. DBCC INDEXDEFRAG can be interrupted. When DBCC INDEXDEFRAG is interrupted, it keeps the optimization already done.
  8. DBCC INDEXDEFRAG operations are logged. You cannot disable the logging.
  9. DBCC INDEXDEFRAG cannot defragment a disabled index.
  10. DBCC INDEXDEFRAG cannot defragment an index with page locking set to OFF.
  11. DBCC INDEXDEFRAG cannot defragment system tables.
  12. DBCC INDEXDEFRAG does not work if indexes are interleaved on disk.
  13. DBCC INDEXDEFRAG will be deprecated in future versions. When? Microsoft has not announced which version of SQL Server will deprecate it.
  14. You should use ALTER INDEX INSTEAD.

DBCC DBREINDEX

  1. DBCC DBREINDEX rebuilds one index or all indexes for a table.
  2. DBCC DBREINDEX is an offline operation.
  3. If low amount of fragmentation, then it's lot slower than DBCC DBREINDEX.
  4. If high amount of fragmentation, then it's lot faster than DBCC DBREINDEX.
  5. DBCC DBREINDEX supports rebuilding clustered indexes.
  6. If DBCC DBREINDEX rebuilds a nonclustered index then it will hold a shared lock on the table.
  7. If DBCC DBREINDEX rebuilds a clustered index then it will hold an exclusive lock on the table.
  8. DBCC REINDEX will be deprecated in future versions. When? Microsoft has not announced which version of SQL Server will deprecate it.
  9. You should use ALTER INDEX INSTEAD.