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:
- 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 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