Transact-SQL Reference

DBCC CHECKIDENT

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 error 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. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be 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:

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 the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin 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

See Also

ALTER TABLE

CREATE TABLE

DBCC

IDENTITY (Property)

USE