Reset Identity column value in SQL Server

We always use Identity Column in our table.Suppose i am deleting some
records from table assume last two records now i want to insert some
new records now identity column value will not be in sequence with same
seed value you will notice some gap between those records.

Let’s try to demonstrate same thing with illustration.


Here ID column is Identity column in table.Now let’s try to delete last two records from table.


So now table is looking like.

 
Now i am trying to insert one fresh record in table.

INSERT INTO dbo.Employee
([First Name],[Last Name],Salary,Location)
VALUES
('Marco','Longman',500000,'India')

Now table status has become


Difference is very clear.It can be restored from 5 but we need to use DBCC command.

DBCC CHECKIDENT ('Employee', reseed, 5)

That’s It
Enjoy Learning.