DBCC CHECKIDENT (Transact-SQL)

DBCC CHECKIDENT (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

DBCC CHECKIDENT (Transact-SQL)

Updated: 17 November 2008

Checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Topic link icon Transact-SQL Syntax Conventions

Syntax

DBCC CHECKIDENT 
( 
        table_name
        [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

Arguments

table_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 comply with the rules for identifiers.

NORESEED: Specifies that the current identity value should not be changed.

RESEED: Specifies that the current identity value should be changed.

new_reseed_value: Is the new value to use as the current value of the identity column.

WITH NO_INFOMSGS: Suppresses all informational messages.

Result Sets

Whether or not any of the options are specified for a table that contains an identity column, DBCC CHECKIDENT returns (values may vary):

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

Changing the Seed Value

The seed value is the value inserted into an identity column for the very 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. For more information, see Creating and Modifying Identifier Columns.

You cannot use DBCC CHECKIDENT to perform the following tasks:

Change the original seed value that was 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, you must 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 is not guaranteed.

Remarks

The specific corrections made to the current identity value depend on the parameter specifications.

DBCC CHECKIDENT command	Identity correction or corrections made
DBCC CHECKIDENT ( table_name, NORESEED )	Current identity value is not reset. 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 ) or 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.
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )	Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or 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. Otherwise, the next row inserted uses new_reseed_value + the current increment value. 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: 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. If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

DBCC CHECKIDENT command

Identity correction or corrections made

DBCC CHECKIDENT ( table_name, NORESEED )

Current identity value is not reset. 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, 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.

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or 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. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

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:

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.
If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

Exceptions

The following table lists conditions when DBCC CHECKIDENT does not 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.	Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) command. or 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.	Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to the desired starting value.

Condition

Reset methods

The current identity value is larger than the maximum value in the table.

Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) command.

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.

Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to the desired starting value.

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. Resetting the current identity value, if it is needed

The following example resets the current identity value, if it is needed, of the Employee table in the AdventureWorks database.

USE AdventureWorks;
GO
DBCC CHECKIDENT ("HumanResources.Employee");
GO

B. Reporting the current identity value

The following example reports the current identity value in the Employee table in the AdventureWorks database, and does not correct the identity value if it is incorrect.

USE AdventureWorks;
GO
DBCC CHECKIDENT ("HumanResources.Employee", NORESEED);
GO

C. Forcing the current identity value to 30

The following example forces the current identity value in the EmployeeID column in the Employee table to a value of 300. Because the table has existing rows, the next row inserted will use 301 as the value, that is, the current identity value plus 1, the current increment value defined for the column.

USE AdventureWorks;
GO
DBCC CHECKIDENT ("HumanResources.Employee", RESEED, 30);
GO

Change History

Release	History
17 November 2008	Changed content: Corrected the definition of new_reseed_value. Added the section in the Remarks about changing the seed value.
14 April 2006	New content: Added text about the results of setting an identity value to a number less than the maximum value in the identity column. Added text to Exceptions section for when all rows are deleted from the table.
5 December 2005	New content: Added text to the description of the new_reseed_value argument.

Changing the Seed Value

Exceptions

A. Resetting the current identity value, if it is needed

B. Reporting the current identity value

C. Forcing the current identity value to 30

Reference

Other Resources

Help and Information