dbcc dbreindex server sql_DBCC DBREINDEX (Transact-SQL)

DBCC DBREINDEX (Transact-SQL)DBCC DBREINDEX (Transact-SQL)

07/16/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

对指定数据库中的表重新生成一个或多个索引。Rebuilds one or more indexes for a table in the specified database.

重要

后续版本的 Microsoft SQL Server 将删除该功能。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.

适用范围：SQL ServerSQL Server(SQL Server 2008SQL Server 2008 到当前版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through current version)

语法Syntax

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

[ WITH NO_INFOMSGS ]

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

table_nametable_name

包含要重新生成的指定索引的表的名称。Is the name of the table containing the specified index or indexes to rebuild. 表名必须遵循有关标识符的规则。Table names must follow the rules for identifiers.

index_nameindex_name

要重新生成的索引名。Is the name of the index to rebuild. 索引名称必须符合标识符规则。Index names must comply with the rules for identifiers. 如果已指定 index_name，则必须指定 table_name。If index_name is specified, table_name must be specified. 如果未指定 index_name 或者该值为“ ”，则重新生成表的所有索引。If index_name is not specified or is " ", all indexes for the table are rebuilt.

fillfactorfillfactor

在创建或重新生成索引时，每个索引页上用于存储数据的空间的百分比。Is the percentage of space on each index page for storing data when the index is created or rebuilt. 创建索引后，fillfactor 将替换填充因子，从而成为该索引以及重新生成的任何其他非聚集索引(因为重新生成了聚集索引)的新默认值。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.

当 fillfactor 为 0 时，DBCC DBREINDEX 将使用上次为索引指定的填充因子值。When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index. 该值存储在 sys.indexes 目录视图中。This value is stored in the sys.indexes catalog view.

如果已指定 fillfactor，则必须指定 index_name。If fillfactor is specified, table_name and index_name must be specified. 如果未指定 fillfactor，则使用默认填充因子 100。If fillfactor is not specified, the default fill factor, 100, is used. 有关详细信息，请参阅 为索引指定填充因子。

WITH NO_INFOMSGSWITH NO_INFOMSGS

取消严重级别从 0 到 10 的所有信息性消息。Suppresses all informational messages that have severity levels from 0 through 10.

备注Remarks

DBCC DBREINDEX 重新生成表的一个索引或为表定义的所有索引。DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. 通过允许动态重新生成索引，可以重新生成强制 PRIMARY KEY 或 UNIQUE 约束的索引，而不必删除并重新创建这些约束。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 可以在一条语句中重新生成表的所有索引。DBCC DBREINDEX can rebuild all the indexes for a table in one statement. 这要比对多条 DROP INDEX 和 CREATE INDEX 语句进行编码更容易。This is easier than coding multiple DROP INDEX and CREATE INDEX statements. 由于这项工作是通过一条语句执行的，因此 DBCC DBREINDEX 自动成为原子性的，而单个 DROP INDEX 和 CREATE INDEX 语句则必须包含在事务中才能成为原子性的。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. 此外，DBCC DBREINDEX 提供了比单个 DROP INDEX 和 CREATE INDEX 语句更多的优化性能。Also, DBCC DBREINDEX offers more optimizations than individual DROP INDEX and CREATE INDEX statements.

与 DBCC INDEXDEFRAG 或具有 REORGANIZE 选项的 ALTER INDEX 不同，DBCC DBREINDEX 是一个脱机操作。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. 为了执行联机索引重新生成，或控制索引重新生成操作期间的并行度，可使用具有 ONLINE 选项的 ALTER INDEX REBUILD 语句。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 .

限制Restrictions

不支持对以下对象使用 DBCC DBREINDEX：DBCC DBREINDEX is not supported for use on the following objects:

系统表System tables

空间索引Spatial indexes

xVelocity 内存优化的列存储索引xVelocity memory optimized columnstore indexes

结果集Result Sets

除非指定了 NO_INFOMSGS(必须指定表名)，否则 DBCC DBREINDEX 将始终返回：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.

权限Permissions

调用方必须拥有此表，或是 sysadmin 固定服务器角色、db_owner 固定数据库角色或 db_ddladmin 固定数据库角色的成员 。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.A. 重新生成索引Rebuilding an index

以下示例使用填充因子 Employee_EmployeeID 对 80 数据库中的 Employee 表重新生成 AdventureWorks 聚集索引。The following example rebuilds the Employee_EmployeeID clustered index with a fill factor of 80 on the Employee table in the AdventureWorks database.

USE AdventureWorks2012;

GO

DBCC DBREINDEX ('HumanResources.Employee', PK_Employee_BusinessEntityID,80);

GO

B.B. 重新生成所有索引Rebuilding all indexes

以下示例使用填充因子值 Employee 对 AdventureWorks 中的 70 表重新生成所有索引。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

另请参阅See Also