What is the Replacement of DBCC DBREINDEX? – Interview Question of the Week #263

Question: What is the replacement of DBCC DBREINDEX in SQL Server?

Answer: Use ALTER INDEX ... REBUILD. It is a short answer, and it is one I still need when reviewing an old client’s maintenance job. DBCC DBREINDEX is deprecated; Microsoft directs us to ALTER INDEX for new work and for a planned replacement of existing scripts.

Replacement of DBCC DBREINDEX: ALTER INDEX syntax

Substitute an index and table that actually exist in your database. The first statement rebuilds one index; the second form rebuilds all indexes on a table when that is truly the reviewed maintenance task.

ALTER INDEX IndexName ON dbo.TableName REBUILD;

ALTER INDEX ALL ON dbo.TableName REBUILD;

You may also see ALTER INDEX ... REORGANIZE. That is a different operation, not a word-for-word replacement for a rebuild. It reorganizes leaf-level pages rather than rebuilding the index. Decide whether an index needs either operation before converting an old job that blindly rebuilds everything each night.

The practical benefit of moving away from DBCC DBREINDEX is access to the supported index-maintenance syntax and its newer options, subject to your SQL Server version, edition, and index type. The work can still use significant log space and compete with application queries, so review the job’s schedule and scope. The interview answer remains simple: ALTER INDEX ... REBUILD replaces DBCC DBREINDEX.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.