How to Reset Identity Column in SQL Server

In this SQL Server tutorial, I will show you how to reset identity column in SQL Server.

You will learn about the identity column and its syntax with why to reset the identity column.

But why you need to reset, there are multiple reason, I have explained some of the reason in this tutorial.

For example, when you remove a large part of the data from the database, in that case, you must reset the identity column.

I also explained what is required to reset the identity column of the table to any seed value. Additionally, I have provided some tips to reset the identity column quickly.

Table of Contents

What Identity Column is?

The identity column is a special column in the table whose values are generated automatically whenever a new record is inserted into the table. In general, this column is also used as the primary key column to uniquely identify each record.

The SQL Server automatically generates the unique values for this column to uniquely identify each record of the table. Now, you know the purpose of the identity column, but why reset it?

There are multiple reasons. The first is data cleaning. Suppose you have deleted a large number of records, so it is necessary to reset the identity column to avoid gaps in the sequence of values in the identity column.

seconds is the database migration, so whenever the database is migrated from one database to another, it must reset the identity column to prevent any conflicts.

The syntax of the identity attribute is given below.

column_name data_type [IDENTITY(seed, increment)]

Where,

column_name: This is the name of the column to which you want to apply the identity attribute.
data_type: Specifies the column’s data type. It can be any valid data type, such as int, bigint, smallint, tinyint, etc.
IDENTITY(seed, increment): It is the identity attribute. It consists of two optional parameters:
- seed: This parameter specifies the starting value of the identity column. It indicates the initial value that is assigned to the first row. If not provided, the default seed value is 1.
- increment: This parameter specifies the increment value by which the identity values will increase for each new row. If not provided, the default increment value is 1.

From the above syntax, you understand how to use the identity attribute. Let’s take an example: Create a table with an identity column and insert some records into it.

CREATE TABLE OrderHistory (
    OrderID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int,
    ProductID int,
    OrderDate date,
    TotalAmount decimal(10, 2)
);

Using the above command, create the OrderHistory table with columns OrderID, CustomerID, ProductID, OrderDate and TotalAmount.

Where OrderID is an identity column representing a unique identifier for each order. It starts from 1 and increments by 1 for each new order. It’s also the primary key of the table.

So, whenever a new record is inserted into the table, the OrderID column value will incremented automatically.

For example, let’s insert the following records into the OrderHistory table.

INSERT INTO OrderHistory (CustomerID, ProductID, OrderDate, TotalAmount)
VALUES
    (101, 201, '2024-04-25', 49.99),
    (102, 202, '2024-04-25', 29.99),
    (103, 203, '2024-04-24', 99.99),
    (104, 204, '2024-04-24', 19.99),
    (105, 205, '2024-04-23', 149.99),
    (101, 206, '2024-04-23', 79.99),
    (102, 207, '2024-04-22', 39.99),
    (103, 208, '2024-04-22', 69.99),
    (104, 209, '2024-04-21', 129.99),
    (105, 210, '2024-04-21', 89.99);

View the table OrderHistory using the code below.

SELECT * FROM OrderHistory;

Before Reset Identity Column in SQL Server Creating a Table with Identity Column

From the above picture, you can see that the OrderID column contains the unique numeric values in sequence; although we haven’t specified these numeric values while inserting the records, SQL Server generates them automatically.

Let’s remove one of the records from the table using the query below.

DELETE FROM OrderHistory
WHERE OrderID = 4

SELECT * FROM OrderHistory;

From the above output, you can see that the records with OrderID are deleted, and there is a gap between the sequence of the values in the OrderID column. For example, after the OrderID 3, the next OrderID is 5, not 4.

How to Reset Identity Column in SQL Server

Suppose you have deleted all the records using the below query.

DELETE FROM OrderHistory;

But when you insert the new record the OrderID value for that record would be 11, not 1.

INSERT INTO OrderHistory (CustomerID, ProductID, OrderDate, TotalAmount)
VALUES
    (101, 201, '2024-04-25', 49.99);

SELECT * FROM OrderHistory;

Deleting All the records Before Reset Identity Column in SQL Server

Look at the above output. The OrderID value for the first record in the table is 11, not 1, but it should be 1 because the table is empty.

So here, you need to reset the identity column OrderIID to start the value from 1; for that SQL Server has a DBCC CHECKIDENT command to check and set the current identity value of a table.

The syntax is given below.

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

Where,

table_name: It is the name of the table whose identity column you want to reset, and 1 is the new starting value for the identity column. RESEED is the keyword.

Now, reset the identity column of the OrderHistory column, for that removes all the records using the query below.

DELECT FROM OrderHistory

Reset the identity column using the DBCC CHEKCIDENT as shown below.

DBCC CHECKIDENT ('OrderHistory', RESEED, 0)

After resetting insert the following records again and check the value of the column OrderID.

INSERT INTO OrderHistory (CustomerID, ProductID, OrderDate, TotalAmount)
VALUES
    (101, 201, '2024-04-25', 49.99),
    (102, 202, '2024-04-25', 29.99),
    (103, 203, '2024-04-24', 99.99),
    (104, 204, '2024-04-24', 19.99);

View the table.

SELECT * FROM OrderHistory;

From the output, you can see that after resetting the identity column, the OrderID column value starts at 1 for the first record, 2 for the second, and so on.

But here is a tip for you: Whenever you want to remove all the records and reset the identity column, I suggest you run the command below.

TRUNCATE TABLE OrderHistory;

The above TRUNCATE table command does two things: first, it removes all the records from the specified table, and second, it resets the identity column.

So whenever you insert a new record, that record will start from 1.

From the above example and concepts, you understand how to reset the identity column in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to reset the identity column in SQL Server using the DBCC CHEKCIDENT command.

You also learned how to use the TRUNCATE TABLE command to remove all the records from the table and reset the identity column.