DBCC CHECKIDENT (T-SQL)

Checks the current identity value for the specified table and, if needed, corrects the identity value.

Syntax

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

Arguments

'table_name': Is the name of the table for which to check the current identity value. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. The table specified must contain an identity column.
NORESEED: Specifies that the current identity value should not be corrected.
RESEED: Specifies that the current identity value should be corrected.
new_reseed_value: Is the value to use in reseeding the identity column.

Remarks

If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Invalid identity information can cause message 2627 when a primary key or unique key constraint exists on the identity column.

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

DBCC CHECKIDENT statement	Identity correction(s) made
DBCC CHECKIDENT ('table_name', NORESEED)	The current identity value is not reset. DBCC CHECKIDENT returns a report indicating the current identity value and what it should be.
DBCC CHECKIDENT ('table_name') or DBCC CHECKIDENT ('table_name', RESEED)	If the current identity value for a table is lower than the maximum identity value stored in the column, it is reset using the maximum value in the identity column.
DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)	The current identity value is set to the new_reseed_value. Use care with this method because it forces the current identity value to be whatever is specified for new_reseed_value. If the value of new_reseed_value is less than the maximum value in the column, error 2627 is generated on subsequent references to the table.

The current identity value can be larger than the maximum value in the table. DBCC CHECKIDENT does not reset the current identity value automatically in this case. To reset the current identity value when it is larger than the maximum value in the column, use either of two methods:

Execute DBCC CHECKIDENT ('table_name', NORESEED) to determine the current maximum value in the column, and then specify that as the new_reseed_value in a DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) statement.
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).

Result Sets

Whether or not any of the options are specified (for a table containing an identity column; this example uses the jobs table of the pubs database), DBCC CHECKIDENT returns this result set (values may vary):

Checking identity information: current identity value '14', current column value '14'.

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

Permissions

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

Examples

A. Reset the current identity value, if needed

This example resets the current identity value, if needed, of the jobs table.

USE pubs

GO

DBCC CHECKIDENT (jobs)

GO

B. Report the current identity value

This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

USE pubs

GO

DBCC CHECKIDENT (jobs, NORESEED)

GO

C. Force the current identity value to 30

This example forces the current identity value in the jobs table to a value of 30.

USE pubs

GO

DBCC CHECKIDENT (jobs, RESEED, 30)

GO