Reseeding/reset the identity of table.

Generally, on inserting the new data into table we get next seed value for identity column. When a row is deleted, we cannot use that identity value again. We can resolve it by reseeding the identity of table as follows:

1. SELECT * FROM Sample_Data

​Number	​Value
​1	​First
​2	​First
​3	​First
​4	​First
​5	​First
​6	​First
​7	​First

2. DELETE FROM Sample_Data WHERE Number > 4

​Number​	​Value
​1	​​First
​2	​​First
​3	​​First
​4	​​First

3. DECLARE @MaxID INT

SELECT @MaxID=MAX(Number) FROM Sample_Data

DBCC CHECKIDENT('Sample_Data',RESEED,@MaxID)

GO

RESULT:-

Checking identity information: current identity value '16'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4. INSERT INTO Sample_Data(Value) VALUES('Second')

   Go 3

​​Number	​​Value​
1​	​​​First
2​	​​​First
3​	​​​First
4​	​​​First
5​	​Second
​6	​Second
7​	​Second

Happy Coding!!