Reset Identity Column Values in SQL

What is an Identity Column?

The Identity column of a table is a column whose value increments consequently. This can be used to create unique identifiers, such as primary keys.

Syntax

Following is the syntax to create an Identity column. The initial setting for identity is represented as IDENTITY (1,1).

IDENTITY [ ( seed , increment ) ]

Where ?

• Seed: The seed determines the initial value of an ID, with a default setting of 1.
• Increment: This denotes the step value for ID increments, which also defaults to 1.

Example

First of all, let's create a table named "Customer" as shown below. In this instance, the Customer_ID column of the table begins at 1, as the default seed value is 1, and each subsequent row increments by 1.

CREATE TABLE Customer (
   Customer_ID INT IDENTITY,
   Customer_Name VARCHAR(200),
   Salary INT
);

Now, lets insert some values into the above table ?

INSERT INTO Customer (Customer_Name, Salary) VALUES ('Aisha', 50000);
INSERT INTO Customer (Customer_Name, Salary) VALUES ('Kabir', 45000);
INSERT INTO Customer (Customer_Name, Salary) VALUES ('Ananya', 52000);
INSERT INTO Customer (Customer_Name, Salary) VALUES ('Rohan', 48000);
INSERT INTO Customer (Customer_Name, Salary) VALUES ('Meera', 48000);

The table will be created as shown below ?

Let's delete a record from the above-created table ?

DELETE FROM Customer WHERE Customer_ID = 4;

If you retrieve the contents of the table after deletion, you can observe that the Customer_ID column is no longer in sequence, so you'll need to reset the Identity Column.

Resetting the Identity Column

You can reset the Identity valuealue Using the DBCC CHECKIDENT Method. Following is the syntax to do so ?

DBCC CHECKIDENT ('table_name', RESEED, new_value);

Note: If we reset the existing records in the table and insert new ones, an error will occur.

• Create a new table as a backup of the main table (i.e., Customer).
• Delete all data from the main table.
• Reset the identity column.
• Re-insert all data from the backup table to the main table.

Example

First of all, let's create a backup table named new_Customer as ?

CREATE TABLE new_Customer 
AS 
SELECT Customer_ID, Customer_Name, Salary FROM Customer;

Now, clear all data from the Customer table and, reset the Identity column.

DELETE FROM Customer;
DBCC CHECKIDENT ('Customer', RESEED, 0);

Finally, insert all the data back into the main table from the backup.

INSERT INTO Customer (Customer_Name, Salary) 
SELECT Customer_Name, Salary 
FROM new_Customer 
ORDER BY Customer_ID ASC;

The resultant table will be as follows ?