DBCC DBREINDEX Statement (version 6.5)

Rebuilds one or more indexes for a table.

For additional syntax information for the DBCC statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

DBCC DBREINDEX (['database.owner.table_name' [, index_name
[, fillfactor [, {SORTED_DATA | SORTED_DATA_REORG}]]]])
[WITH NOINFOMSGS]

where

database.owner.table_name

Specifies the table for which to rebuild the index. If you specify the database name or owner the entire name must be enclosed in single quotation marks. If only the table name is specified then the quotes are optional.

index_name

Specifies the name of the index to rebuild. Specifying ' ' or no index_name rebuilds all indexes.

fillfactor

Specifies how full each index page is to be created. If 0 (zero) is entered for this parameter then the original fillfactor is used.

SORTED_DATA | SORTED_DATA_REORG

Specify that the sort performed when a clustered index is created can be eliminated because the data is sorted. Both options verify that the data has been sorted by checking each index value to determine whether it is a higher value than the previous one.

If any row fails this check, the DBCC DBREINDEX statement terminates. You can either fix the data or rerun the DBCC DBREINDEX statement without the SORTED_DATA or SORTED_DATA_REORG options.

When the data satisfies the checks, the SORTED_DATA option will always be faster than the SORTED_DATA_REORG option because the data is not copied and nonclustered indexes are not rebuilt.

SORTED_DATA_REORG differs from SORTED_DATA because it physically reorganizes the data. This option is useful when a FILLFACTOR is specified to compact or expand the pages on which a table is stored. The effects of these options change slightly if used with the ON segment_name option.

Reorganizing the data is a good idea when a table becomes fragmented. To determine whether or not a table is fragmented, use the DBCC SHOWCONTIG statement.

Important If you specify one of the optional parameters (ind_name, fillfactor, or SORTED_DATA), you must specify all the parameters preceding it in the parameter list.

WITH NO_INFOMSGS

Prevents the printing of informational messages (severity 1 through 10).

Remarks

The DBCC DBREINDEX statement rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes that enforce PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create constraints. This means that an index can be rebuilt without having to know the table's structure or constraints. This could occur after a bulk copy of data into the table.

If the index is a clustered index, all nonclustered indexes will also be reindexed with the same supplied fillfactor. This statement is completely recoverable and atomic for all of the indexes being rebuilt.

The DBCC DBREINDEX statement is not supported for system tables.

Examples

A.    Rebuild an Index

This example rebuilds the au_nmind 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 a table by using the existing fillfactor values and physically reorganizes the data.

DBCC DBREINDEX (authors, '', 0, SORTED_DATA_REORG)
  

Permission

Only the system administrator, database owner, and table owner can use this statement.