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.
![Check Current Identity Value Using DBCC CHECKIDENT](https://www.mytecbits.com/wp-content/uploads/Reset-Identity-Seed-1.jpg)
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.
![Reset Identity Seed In SQL Server](https://www.mytecbits.com/wp-content/uploads/Reset-Identity-Seed-2.jpg)
Reference
- About database console command DBCC CHECKIDENT at Microsoft Docs.