Often while testing the applications during the development phase you may need to insert data to the database tables, delete them and re-insert them multiple times. Sometimes you may need to reset the auto incremented identity column to start again from the beginning or from a specific number again. Here, we will see how to reset identity seed in SQL Server.
To re-seed the identity column, you can use use the DBCC CHECKIDENT management comment. Using CHECKIDENT, you can specify a new identify value and re-seed. You can also checking the current identify value and decide your re-seeding accordingly.
Checking Current Identity Value
Before re-seeding, it is a good practice to check the current and the maximum identity value. He is how do check it:
Syntax
DBCC CHECKIDENT ( <table>, NORESEED )
Where:
<table> = Name of the table which needs to be checked.
Returns:
A message with the value of current identity and current column.
Example
In this example you can see the current identity value is higher than the current column value.
DBCC CHECKIDENT( [MTB_Table_E], NORESEED)
/* Result */
Checking identity information: current identity value '98', current column value '50'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Reset Identity Seed
Now, we will see how to reseed the identity using CHECKIDENT.
Syntax
DBCC CHECKIDENT ( <table>, RESEED, <new-seed> )
Where:
<table> = Name of the table which needs to be checked.
<new-seed> = New re-seed value for the identity column.
Returns:
A message or exception
Example
DBCC CHECKIDENT( [MTB_Table_E], NORESEED)
DBCC CHECKIDENT( [MTB_Table_E], RESEED, 50)
DBCC CHECKIDENT( [MTB_Table_E], NORESEED)
/* Result */
Checking identity information: current identity value '98', current column value '50'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '98'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '50', current column value '50'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Reference
- About database console command DBCC CHECKIDENT at Microsoft Docs.