DBCC CHECKIDENT (Transact-SQL)DBCC CHECKIDENT (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

检查 SQL ServerSQL Server 中指定表的当前标识值,如有必要,则更改标识值。Checks the current identity value for the specified table in SQL ServerSQL Server and, if it's needed, changes the identity value. 还可以使用 DBCC CHECKIDENT 为标识列手动设置新的当前标识值。You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

文章链接图标 Transact-SQL 语法约定Article link icon Transact-SQL Syntax Conventions

语法Syntax


-- Syntax for SQL Server and Azure SQL Database  

DBCC CHECKIDENT
 (
    table_name  
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  
)  
[ WITH NO_INFOMSGS ]  
-- Syntax for Azure SQL Data Warehouse
DBCC CHECKIDENT   
 (   
    table_name  
        [RESEED, new_reseed_value ]   
)  
[ WITH NO_INFOMSGS ]  

参数Arguments

table_nametable_name
是要对其当前标识值进行检查的表名。Is the name of the table for which to check the current identity value. 指定的表必须包含标识列。The table specified must contain an identity column. 表名必须遵循有关标识符的规则。Table names must follow the rules for identifiers. 两个或三个部分的名称必须进行分隔,例如 'Person.AddressType' 或 [Person.AddressType]。Two or three part names must be delimited, such as 'Person.AddressType' or [Person.AddressType].

NORESEEDNORESEED
指定不应更改当前标识值。Specifies that the current identity value shouldn't be changed.

RESEEDRESEED
指定应该更改当前标识值。Specifies that the current identity value should be changed.

new_reseed_valuenew_reseed_value
用作标识列的当前值的新值。Is the new value to use as the current value of the identity column.

WITH NO_INFOMSGSWITH NO_INFOMSGS
取消显示所有信息性消息。Suppresses all informational messages.

RemarksRemarks

对当前标识值所做的具体更正取决于参数规范。The specific corrections made to the current identity value depend on the parameter specifications.

DBCC CHECKIDENT 命令DBCC CHECKIDENT command 标识更正或所做的更正Identity correction or corrections made
DBCC CHECKIDENT ( table_name, NORESEED )DBCC CHECKIDENT ( table_name, NORESEED ) 不重置当前标识值。Current identity value is not reset. DBCC CHECKIDENT 将返回标识列的当前标识值和当前最大值。DBCC CHECKIDENT returns the current identity value and the current maximum value of the identity column. 如果这两个值不相同,则应重置标识值,以避免值序列中的潜在错误或空白。If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values.
DBCC CHECKIDENT ( table_name )DBCC CHECKIDENT ( table_name )

或多个or

DBCC CHECKIDENT ( table_name, RESEED )DBCC CHECKIDENT ( table_name, RESEED )
如果表的当前标识值小于标识列中存储的最大标识值,则使用标识列中的最大值对其进行重置。If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column. 请参阅后面的“例外”一节。See the 'Exceptions' section that follows.
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) 将当前标识值设置为 new_reseed_valueCurrent identity value is set to the new_reseed_value. 如果自从创建表以来未在表中插入任何行,或者已使用 TRUNCATE TABLE 语句删除所有行,则在运行 DBCC CHECKIDENT 之后插入的第一行将使用 new_reseed_value 作为标识。If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. 如果行位于表中,或如果已通过使用 DELETE 语句删除了所有行,则下一个插入行将使用 new_reseed_value + 当前增量值。If rows are present in the table, or if all rows have been removed by using the DELETE statement, the next row inserted uses new_reseed_value + the current increment value. 如果事务插入一个行并且稍后回滚,则下一个插入行使用 new_reseed_value + 当前增量值,就像已删除该行一样 。If a transaction inserts a row and is later rolled back, the next row inserted uses new_reseed_value + the current increment value as if the row had been deleted. 如果该表不为空,那么将标识值设置为小于标识列中的最大值的数字时,将会出现下列情况之一:If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:

-如果标识列中存在 PRIMARY KEY 或 UNIQUE 约束,则随后在表中执行插入操作时将生成错误消息 2627,原因是生成的标识值将与现有值冲突。-If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

-如果不存在 PRIMARY KEY 或 UNIQUE 约束,则随后的插入操作将产生重复的标识值。-If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

异常Exceptions

下表列出了 DBCC CHECKIDENT 不自动重置当前标识值时的条件,并提供了重置该值的方法。The following table lists conditions when DBCC CHECKIDENT doesn't automatically reset the current identity value, and provides methods for resetting the value.

条件Condition 重置方法Reset methods
当前标识值大于表中的最大值。The current identity value is larger than the maximum value in the table. 执行 DBCC CHECKIDENT (table_name , NORESEED) 以确定列中的当前最大值。Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column. 接下来,在 DBCC CHECKIDENT (table_name , RESEED,new_reseed_value ) 命令中指定该值作为 new_reseed_value 。Next, specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) command.

-或--OR-

执行 DBCC CHECKIDENT (table_name, RESEED,new_reseed_value),其中 new_reseed_value 设置为非常低的值,然后运行 DBCC CHECKIDENT (table_name, RESEED) 以更正值。Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value.
删除表中的所有行。All rows are deleted from the table. 执行 DBCC CHECKIDENT (table_name, RESEED,new_reseed_value),其中 new_reseed_value 设置为新起始值 。Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to the new starting value.

更改种子值Changing the Seed Value

种子值是针对加载到表中的第一行插入到标识列的值。The seed value is the value inserted into an identity column for the first row loaded into the table. 所有后续行都包含当前标识值和增量值,其中当前标识值是为当前表或视图生成的最新标识值。All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.

不能将 DBCC CHECKIDENT 用于下列任务:You can't use DBCC CHECKIDENT for the following tasks:

  • 更改创建表或视图时为标识列指定的原始种子值。Change the original seed value specified for an identity column when the table or view was created.

  • 重设表或视图中的现有行的种子值。Reseed existing rows in a table or view.

若要更改原始种子值并重设所有现有行的种子值,请删除并重新创建标识列,然后为标识列指定新的种子值。To change the original seed value and reseed any existing rows, drop the identity column and recreate it specifying the new seed value. 当表包含数据时,还会将标识号添加到具有指定种子值和增量值的现有行中。When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. 无法保证行的更新顺序。The order in which the rows are updated isn't guaranteed.

结果集Result Sets

无论是否为包含标识列的表指定了任何选项,DBCC CHECKIDENT 都会为所有操作(除了一个操作)返回以下消息。Whether or not you specify any options for a table that contains an identity column, DBCC CHECKIDENT returns the following message for all operations except one. 该操作指定新的种子值。That operation is specifying a new seed value.

Checking identity information: current identity value '\<current identity value>', current column value '\<current column value>'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

在 DBCC CHECKIDENT 用于通过使用 RESEED new_reseed_value 来指定新的种子值时,将返回以下消息。When DBCC CHECKIDENT is used to specify a new seed value by using RESEED new_reseed_value, the following message is returned.

Checking identity information: current identity value '\<current identity value>'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

权限Permissions

调用方必须拥有包含此表的架构,或者是 sysadmin 固定服务器角色、db_owner 固定数据库角色或 db_ddladmin 固定数据库角色的成员。Caller must own the schema that contains 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.

Azure SQL 数据仓库需要 db_owner 权限 。Azure SQL Data Warehouse requires db_owner permissions.

示例Examples

A.A. 在需要时重置当前标识值Resetting the current identity value, if it's needed

以下示例在需要时重置 AdventureWorks2012AdventureWorks2012 数据库中指定表的当前标识值。The following example resets the current identity value, if it's needed, of the specified table in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType');  
GO  

B.B. 报告当前标识值Reporting the current identity value

以下示例报告 AdventureWorks2012AdventureWorks2012 数据库的指定表中的当前标识值,但如果该标识值不正确,不会进行更正。The following example reports the current identity value in the specified table in the AdventureWorks2012AdventureWorks2012 database, and doesn't correct the identity value if it's incorrect.

USE AdventureWorks2012;
GO  
DBCC CHECKIDENT ('Person.AddressType', NORESEED);
GO  

C.C. 强制将当前标识值设为新值Forcing the current identity value to a new value

以下示例强制将 AddressTypeID 表中的 AddressType 列中的当前标识值设置为 10。The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. 因为该表有现有行,因此下一个插入行将使用 11 作为值,即为该列定义的新的当前标识值加上 1(该列的当前增量值)。Because the table has existing rows, the next row inserted will use 11 as the value – the new current identity value defined for the column plus 1 (which is the column's increment value).

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO  

D.D. 重置空表上的标识值Resetting the identity value on an empty table

以下示例在从表中删除所有记录后,强制将 ErrorLog 表中的 ErrorLogID 列中的当前标识值设置为 1。The following example forces the current identity value in the ErrorLogID column in the ErrorLog table to a value of 1, after deleting all records from table. 因为该表没有现有行,因此下一个插入行将使用 1 作为值,即当前标识值(而不会加上为该列定义的增量值)。Because the table has no existing rows, the next row inserted will use 1 as the value, that is, the new current identity value, without adding the increment value defined for the column.

USE AdventureWorks2012;  
GO  
TRUNCATE TABLE dbo.ErrorLog
GO
DBCC CHECKIDENT ('dbo.ErrorLog', RESEED, 1);  
GO  

另请参阅See Also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
IDENTITY(属性)(Transact-SQL)IDENTITY (Property) (Transact-SQL)
复制标识列Replicate Identity Columns
USE (Transact-SQL)USE (Transact-SQL)
IDENT_SEED (Transact-SQL)IDENT_SEED (Transact-SQL)
IDENT_INCR (Transact-SQL)IDENT_INCR (Transact-SQL)