DBCC DBREINDEX (T-SQL)

Rebuilds one or more indexes for a table in the specified database.

Syntax

DBCC DBREINDEX
    (    [ 'database.owner.table_name' [, index_name [, fillfactor ] ] ]
    ) [WITH NO_INFOMSGS]

Arguments

'database.owner.table_name'

Is the name of the table for which to rebuild the specified index(es). Database, owner, and table names must conform to the rules for identifiers. For more information, see Using Identifiers. The entire database.owner.table_name must be enclosed in single quotation marks (‘) if either the database or owner parts are supplied. The single quotation marks are not necessary if only table_name is specified.

index_name

Is the name of the index to rebuild. Index names must conform to the rules for identifiers. 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. fillfactor replaces the original fillfactor as the new default for the index and for any other nonclustered indexes that are rebuilt because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created.

WITH NO_INFOMSGS

Suppresses all informational messages (with severity levels from 0 through 10).

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 table’s structure or constraints, which could occur after a bulk copy of data into the table.

If either index_name or fillfactor is specified, all preceding parameters must also be specified.

DBCC DBREINDEX can rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic. Also, DBCC DBREINDEX can take advantage of more optimizations with DBCC DBREINDEX than it can with individual DROP INDEX and CREATE INDEX statements.

DBCC DBREINDEX is not supported for use on system tables.

Result Sets

Whether or not any of the options (except NO_INFOMSGS) are specified (the table name must be specified), DBCC DBREINDEX returns this result set; this example uses the authors table of the pubs database (values will vary):

Index (ID = 1) is being rebuilt.

Index (ID = 2) is being rebuilt.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC DBREINDEX returns this result set (message) if the NO_INFOMSGS option is specified:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC DBREINDEX permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, or the table owner, and are not transferable.

Examples

A. Rebuild an index

This example rebuilds the au_nmind clustered index with a fillfactor of 80 on the authors table in the pubs database.

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)

B. Rebuild all indexes

This example rebuilds all indexes on the authors table using a fillfactor value of 70.

DBCC DBREINDEX (authors, '', 70)

See Also