DBCC DBREINDEX (Transact-SQL)
Rebuilds one or more indexes for a table in the specified database.
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
DBCC DBREINDEX
(
table_name
[ , index_name [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]
Arguments
- table_name
Is the name of the table for which to rebuild the specified index or indexes. Table names must follow the rules for identifiers. database.
- index_name
Is the name of the index to rebuild. Index names must comply with the rules for identifiers. If index_name is specified, table_name must be specified. If index_name is not specified or is specified as '', all indexes for the table are rebuilt.
fillfactor
Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. fillfactor replaces the fill factor when the index was created, becoming the new default for the index and for any other nonclustered indexes rebuilt because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index. This value is stored in the sys.indexes catalog view.If fillfactor is specified, table_name and index_name must be specified. If fillfactor is not specified, the default fill factor, 100, is used. For more information, see Fill Factor.
- WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.
Result Sets
Whether any one of the options, except NO_INFOMSGS, is specified (the table name must be specified), DBCC DBREINDEX returns:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Remarks
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 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 the advantage of 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. Use the ALTER INDEX REBUILD statement with the ONLINE option to perform an index rebuild online, or to control the degree of parallelism during the index rebuild operation.
For more information about selecting a method to rebuild or reorganize an index, see Reorganizing and Rebuilding Indexes .
DBCC DBREINDEX is not supported for use on system tables.
Permissions
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Examples
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.
USE AdventureWorks;
GO
DBCC DBREINDEX ("HumanResources.Employee", PK_Employee_EmployeeID,80);
GO
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 AdventureWorks;
GO
DBCC DBREINDEX ("HumanResources.Employee", " ", 70);
GO
See Also
Reference
ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
DBCC (Transact-SQL)
sys.indexes (Transact-SQL)
sys.dm_db_index_physical_stats
ALTER INDEX (Transact-SQL)