Go to Comments





How to reset identity value in sql server


Created: 27/02/2015 09:20:26 p.m.





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
1100
2          200
3300
4400


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:

ID       AMOUNT
1100
2200
3           300
5500



So to prevent this after delete the number 4, just run this code in the proper table

First delete number 4:

ID       AMOUNT
1100
2          200
3300

Second:

DBCC CHECKIDENT ("[dbo].[cuentas]", RESEED, 3)

and the result will be 

ID       AMOUNT
1100
2          200
3300
4400


This will specify that the current identity column value have number 3 and the next will be number 4






Comments: