How To Reset Identity Column Values In SQL

An Identity Column in SQL is an auto-incrementing column used to generate unique values for each row inserted into a table. This feature is commonly used in tables that require a unique identifier, such as primary keys. The identity column allows the database to automatically handle the generation of unique values without manual intervention from the user.

In this article, we will explain the concept of identity columns, their syntax, managing identity values, and resetting the identity column with examples and outputs. This article is designed to help developers effectively use identity columns to manage unique data in SQL databases.

What is an Identity Column in SQL?

The identity column is a special type of table column that automatically generates unique values for each row. A user generally cannot insert a value into an identity column. These columns are usually used to generate unique identifiers or primary key values without requiring explicit input for every insertion.

Once defined, the identity column generates values based on the specified seed (starting value) and increment (value by which it increases). When defining an identity column in a table, we specify the seed and increment values.

Syntax

IDENTITY [ ( seed , increment ) ]

Key Terms

• 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.

Creating a Table with an Identity Column

When creating a table with an identity column, the identity property automatically generates unique values for each new row. This ensures that each record in the table has a unique identifier, which is especially useful for primary keys. 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.

Query:

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

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);

SELECT * FROM school;

Output

School Table

Managing Identity Column Values

Managing identity column values involves updating or resetting the automatically generated values when necessary, especially after operations like record deletion. After deleting a record, the identity column values may no longer be sequential, requiring actions to maintain consistent and predictable numbering.

Deleting a Record

When rows are deleted, the identity column values remain unchanged, leading to non-sequential numbering.

DELETE FROM school WHERE student_id = 4;

View Updated Records

SELECT * FROM school;

Output

Managing Identity Column Values

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

How to Reset the Identity Column in SQL

In some scenarios, we may need to reset the identity column. For example, after deleting rows, we might want to reset the identity values so that new rows start from a specific number or maintain sequential integrity.

Using DBCC CHECKIDENT to Reset Identity Values

We can use the DBCC CHECKIDENT command in SQL Server to reset the identity column’s value.

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.

Example: Resetting Identity Column After Deleting Rows

Let’s say we have the School table with some data, and we want to reset the student_id after deleting a row.

Step 1: Create a Backup Table

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

Step 2: Delete All Data from the Main Table

DELETE FROM school;

Step 3 : Reset the Identity column.

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

Step 4: Re-insert Data from Backup Table.

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

Step 5: View the Reset Table Records

SELECT * FROM school;

Output

Resetting identity Column after deleting rows

Conclusion

In this guide, we covered the concept of identity columns in SQL, how to define and use them, and how to reset the identity values if necessary. The identity column is a powerful feature that simplifies the process of creating unique identifiers automatically, without needing to manually insert values.

Using an identity column is essential for scenarios where we need sequential or unique values, such as primary keys. Understanding how to reset the identity column using the DBCC CHECKIDENT method also allows us to maintain data integrity and ensure our records follow a predictable sequence.

thesahilrai

Follow

Improve

Article Tags :

• Databases
• SQL
• DBMS-SQL
• SQL-Server