How to reset identity value in sql server
Back to learning
Created: 27/02/2015
The next value will insert 5 in ID column, but if you delete the number 4, the next insert will take number 5 like this:
How to reset identity value in sql server (RESEED)
If you need to set (specify) the next identity value in SQL, for example:
Situation:
ID AMOUNT
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
ID AMOUNT
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 5 | 500 |
So to prevent this after delete the number 4, just run this code in the proper table
First delete number 4:
ID AMOUNT
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
Second: |
DBCC CHECKIDENT ("[dbo].[cuentas]", RESEED, 3)
and the result will be
ID AMOUNT
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
This will specify that the current identity column value have number 3 and the next will be number 4