DBCC DBREINDEX (Transact-SQL)
Rebuilds one or more indexes for a table in the specified database.
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead.
Applies to: SQL Server (SQL Server 2008 through current version).
DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means that an index can be rebuilt without knowing the structure of a table or its constraints. This might occur after a bulk copy of data into the table.
DBCC DBREINDEX can rebuild all the indexes for a table in one statement. This is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is performed by one statement, DBCC DBREINDEX is automatically atomic, whereas individual DROP INDEX and CREATE INDEX statements must be included in a transaction to be atomic. Also, DBCC DBREINDEX offers more optimizations than individual DROP INDEX and CREATE INDEX statements.
Unlike DBCC INDEXDEFRAG, or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline. To perform an index rebuild online, or to control the degree of parallelism during the index rebuild operation, use the ALTER INDEX REBUILD statement with the ONLINE option.
For more information about selecting a method to rebuild or reorganize an index, see Reorganize and Rebuild Indexes .
DBCC DBREINDEX is not supported for use on the following objects:
xVelocity memory optimized columnstore indexes
Unless NO_INFOMSGS is specified (the table name must be specified), DBCC DBREINDEX always returns:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
A. Rebuilding an index
The following example rebuilds the Employee_EmployeeID clustered index with a fill factor of 80 on the Employee table in the AdventureWorks database.
B. Rebuilding all indexes
The following example rebuilds all indexes on the Employee table in AdventureWorks by using a fill factor value of 70.
USE AdventureWorks2012; GO DBCC DBREINDEX ('HumanResources.Employee', ' ', 70); GO