SQL Server - IDENTITY - How to change/reset the IDENTITY values?

SQL Server - IDENTITY - How to change/reset the IDENTITY values?

In the previous post, we saw that the IDENTITY values can go out of sequence in several cases. Some times, you might decide to delete all rows from a table, and start filling fresh data. At this time, you might prefer to reset the IDENTITY value to start a new sequence.

Run the following script to create a sample table.

IF OBJECT_ID('Employees','U') IS NOT NULL
   DROP TABLE Employees
 
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

Let us now delete all rows from this table and insert a new row.

DELETE FROM Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
3           Jacob
*/

As we figured earlier, the IDENTITY value started with 3, not with 1. Let us now see how we can reset the IDENTITY value back to 1.

TRUNCATE TABLE resets IDENTITY values

When you truncate a table, the IDENTITY value will be RESET. Here is an example:

TRUNCATE TABLE Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
*/

Note that the new IDENTITY value started from 1.

DBCC CHECKIDENT

You can use DBCC CHECKIDENT to reset the IDENTITY seed. Before we run the example, let us rebuild the table again.

IF OBJECT_ID('Employees','U') IS NOT NULL
   DROP TABLE Employees
 
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

Now, let us try DBCC CHECKIDENT

DELETE FROM Employees
DBCC CHECKIDENT('Employees',RESEED, 0)
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
*/

It is also possible to change the NEXT identity value to a value of your choice. The following example changes the current identity value to be 99, so that the next value generated will be 100.

DBCC CHECKIDENT('Employees',RESEED, 99)
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
100         Bob
*/

Source:

http://beyondrelational.com/blogs/jacob/archive/2009/01/30/sql-server-identity-how-to-change-reset-the-identity-values.aspx