How to Reset Identity Column Values in SQL

Introduction:

The uniqueness guarantees of such databases are guaranteed by their identification columns, which automatically create the values. Sometimes environments may emerge where situations in which to revert to these settings are available. In this article, we will explore and cover the content which includes the identity column values of the SQL code samples and also the approaches to ensure the integrity of the data while regaining the identity column values.

How to Reset Identity Column Values in SQL

Understanding Identity Columns:

In some cases, identity columns will auto-increment with each new line in a table. As we can see below, constructing the following simple table with an identity column as the first column.

Syntax :

  • The default value for identity is IDENTITY (1,1).
  • The seed is the initial value of an ID. The default value is 1.
  • Increment represents the ID's incremental value, with a default value of 1.

For Example:

Step 1 : First, make a table and name it "office."

As the default value of the seed is 1, the 'employee_id' column of the table begins at 1 and increases by 1 for each entry.

Step 2 : Fill up the table "office" with some data.

Step 3: We can execute the following code to view the data contained in the table "office":

Output:

How to Reset Identity Column Values in SQL

Step 4 : We'll remove this entry.

Step 5 : Check the data in the table.

Output:

How to Reset Identity Column Values in SQL

We must reset the Identity Column as we can now see that the employee_id column is out of order.

Using the DBCC CHECKIDENT Method to Reset the Identity Value:

Here, we can execute the DBCC CHECKIDENT procedure to reset the Identity column in SQL Server.

Syntax :

Note : The table will display an error if we reset the current records and add new ones.

Thus, we must:

  • As a backup to the primary table (the office), create a new table.
  • Eliminate every entry from the primary table.
  • Reset the identification column immediately.
  • Again insert the whole values of the backup table into the primary table.

Step 6 : Create a backup table called "new_office" in this step.

Step 7 : Delete each item of office data.

Step 8 : Reset the Identity column in this step.

Step 9 : Put all of the data in the primary database from the backup table.

Step 10 : Check the table's records

Output :

How to Reset Identity Column Values in SQL

Here is a way to reset the values of the Identity Column in SQL.

Conclusion:

To guarantee data integrity, a methodical methodology is required when resetting identity column values in SQL. Administrators may conduct reset procedures with confidence by using SQL code samples and adhering to recommended practices.






Latest Courses