Skip to content
Related Articles

Related Articles

Save Article
Improve Article
Save Article
Like Article

How To Reset Identity Column Values In SQL

  • Difficulty Level : Hard
  • Last Updated : 22 Jun, 2021

What is ‘Identity Column’ ?

Identity column of a table is a column whose value increases automatically. A user generally cannot insert a value into an identity column. A table can have only one column that is defined with the identity attribute.

Syntax :

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

IDENTITY [ ( seed , increment ) ]

Default value of identity is IDENTITY (1,1).

Seed : The seed represents the starting value of an ID and the default value of seed is 1.



Increment : It will represent the incremental value of the ID and the default value of increment is 1.

For Example :

Step 1 : Create a table named school.

CREATE TABLE school (
student_id INT IDENTITY,
student_name VARCHAR(200),
marks INT
);

Here, the ‘student_id’ column of the table starts from 1 as the default value of seed is 1 and each row is incremented by 1.

Step 2 : Insert some value into a table.

INSERT INTO school (student_name, marks) VALUES ('Sahil', 100);
INSERT INTO school (student_name, marks) VALUES ('Raj', 78);
INSERT INTO school (student_name, marks) VALUES ('Navneet', 80);
INSERT INTO school (student_name, marks) VALUES ('Rahul', 75);
INSERT INTO school (student_name, marks) VALUES ('Sudeep', 82);
INSERT INTO school (student_name, marks) VALUES ('Azaan', 75);

Step 3 : To see the records in the table ‘school’ , we can use the following code:

SELECT * FROM school;

Output :

student_idstudent_namemarks
1Sahil100
2Raj78
3Navneet80
4Rahul75
5Sudeep82
6Azaan75

Step 4 : Lets delete a record.



DELETE FROM school WHERE student_id = 4;

Step 5 : To see the records in the table.

SELECT * FROM school;

Output :

student_idstudent_namemarks
1Sahil100
2Raj78
3Navneet80
5Sudeep82
6Azaan75

Now, you can see that the student_id column is not in order, So you have to reset the Identity Column. 

Reset the Identity Value Using the DBCC CHECKIDENT Method :

Here, to reset the Identity column column in SQL Server you can use DBCC CHECKIDENT method.

Syntax :

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

Note : If we reset the existing records in the table and insert new records, then it will show an error.

So, we need to :

  • Create a new table as a backup of the main table (i.e. school).
  • Delete all the data from the main table.
  • And now reset the identity column.
  • Re-insert all the data from the backup table to main table.

Step 6 : Create backup table named ‘new_school’ .

CREATE TABLE new_school AS SELECT student_id, student_name, marks FROM school;

Step 7 : Delete all the data from school.

DELETE FROM school;

Step 8 : Reset the Identity column.

DBCC CHECKIDENT ('school', RESEED, 0);

Step 9 : Re-insert all the data from the backup table to main table.

INSERT INTO school (student_name, marks) SELECT student_name, marks FROM new_school ORDER BY student_id ASC;

Step 10 : See the records of the table.

SELECT * FROM school;

Output :

student_idstudent_namemarks
1Sahil100
2Raj78
3Navneet80
4Sudeep82
5Azaan75

This is how you can reset Identity Column values in SQL .

My Personal Notes arrow_drop_up
Recommended Articles
Page :