DBCC CHECKIDENT
Check the current identity value of the specified table, if necessary, correct the identifier value.
Syntax DBCC CHECKIDENT ('Table_name' [, {Noreed | {Reseed] [, new_reseed_value]}])
The parameter 'Table_Name' is a table name to check its current identifier value. The specified table must contain identity columns.
NoreED specifies that the current identity value should not be corrected.
Reseed specifies that the current identifier value should be correct.
NEW_RESEED_VALUE is the value to be used when reassocating the value in the identity column.
Note If necessary, DBCC CHECKIDENT will be more correct of the current identity value. However, if the identity column is created using the Not for Replication clause (in the CREATE TABLE or ALTER TABLE statement), the current identification value is not corrected. If the identity column has a primary key or a unique key constraint, the invalid identification information may cause the error message 2627.
DBCC CHECKIDENT ('Table_name', NoreED) does not reset the current identification value. DBCC CHECKIDENT returns a report that indicates the current identifier value and the corresponding identity value. DBCC CHECKIDENT ('Table_name') or DBCC CHECKIDENT ('Table_name', Reseed) If the current identification value of the table is smaller than the maximum identity value stored in the column, the maximum value in the identity column resets it. DBCC Checkident ('Table_name', Reseed, New_reseed_Value) The current value is set to new_reseed_value. If the row is not inserted into the table since the creation table, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as an identifier. Otherwise, the next inserted row will use new_reseed_value 1. If the value of new_reseed_value is smaller than the maximum value in the identity column, the 2627 error message will be generated when referenced to the table.
The current identification value can be greater than the maximum in the table. In this case, DBCC CHECKIDENT does not automatically reset the current identity value. To reset the current identifier value when the current identification value is greater than the maximum value in the column, use any of two ways:
Perform DBCC CHECKIDENT ('Table_name', NoreED) to determine the current maximum in the column, and then specify this value as new_reseed_value using DBCC CHECKIDENT.
Set new_reseed_value to perform DBCC CHECKIDENT ('Table_Name', Reseed, New_reseed_Value, then run DBCC Checkident ('Table_Name', Reseed). Result Set No matter whether you specify any options (for tables containing identity columns; the next example uses the JOBS table of the Pubs database), DBCC Checkident returns the following result set (value may change):
Checking identity information:. Current identity value '14', current column value '14'.DBCC execution completed If DBCC printed error messages, contact your system administrator privileges DBCC CHECKIDENT permissions default to the table owner, sysadmin fixed server role and db_owner fixed. Members of the database role are not transferable.
Example A. If necessary, reset the current identifier value to reset the current identifier value of the JOBS table without the necessary case.
Use Pubsgodbcc Checkident (Jobs) Go
B. Report the current identifier value to report the current identification value in the JOBS table; if the identity value is incorrect, it is not corrected.
Use Pubsgodbcc Checkident (Jobs, Noresed) Go
C. Forced the current identification value of 30 to force the current identifier value in the JOBS table in the next example.
Use Pubsgodbcc Checkident (Jobs, Reseed, 30) Go
