How to reset an IDENTITY column value In SQL Server?
- How-Tos FAQs
- December 17, 2018
Learn how to reset an IDENTITY column value In SQL Server.
An IDENTITY column is an auto-incrementing column. This column cannot be updated directly. If we need to reset this column, we have two different options.
- TRUNCATE
- DBCC CHECKIDENT
Let us create a table below to demonstrate the use of both of the above methods.
-- Create a table ‘orders’ with IDENTITY column ‘order_id’
CREATE TABLE orders (
order_id INT IDENTITY(1, 1),
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
INSERT INTO orders
SELECT 'Jack', '2020-02-03', 4 UNION ALL
SELECT 'Rose', '2020-01-08', 19 UNION ALL
SELECT 'John', '2019-02-06', 40 UNION ALL
SELECT 'Doe', '2020-01-09', 97 ;
SELECT * FROM orders
-- Output
-- since we have inserted 4 rows in the table, the current value of the IDENTITY column is 4
-- if we insert a new row to the table, the value of IDENTITY column will be 5
order_id customer_name order_date total_orders
------------------------------------------------------------------------
1 Jack 2020-02-03 00:00:00.000 4
2 Rose 2020-01-08 00:00:00.000 19
3 John 2019-02-06 00:00:00.000 40
4 Doe 2020-01-09 00:00:00.000 97-- Lets DELETE all the data of the table
DELETE FROM orders
-- INSERT new row of data
INSERT INTO orders
SELECT 'Dave', '2020-04-05', 3
SELECT * FROM orders
-- Output
-- The value of order_id is 5, even if we have deleted all the rows from the table.
order_id customer_name order_date total_orders
--------------------------------------------------------------------------------
5 Dave 2020-04-05 00:00:00.000 3- TRUNCATE
The TRUNCATE command performs two operations:
a) Delete all the data from a table
b) Reset the IDENTITY column to 0
-- TRUNCATE command deletes all the data from the table. We cannot selectively delete the rows using WHERE condition
TRUNCATE TABLE orders
INSERT INTO orders
SELECT 'Daniel', '2018-04-05', 9
SELECT * FROM orders
-- Output
-- The IDENTITY column is now reset to 0 and the value of newly inserted column is set to 1
order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Daniel 2018-04-05 00:00:00.000 92. DBCC CHECKIDENT
We can not use TRUNCATE in call the case like it cannot be used with the table with a foreign key. Also, TRUNCATE can only reset the IDENTITY to value 0. If we need to reseed it to any other value, we should use the DBCC CHECKIDENT command.
-- DELETE all the data from the table
DELETE FROM orders
-- Reseed the IDENTITY column of the the to 0
DBCC CHECKIDENT('orders', RESEED, 0);
INSERT INTO orders
SELECT 'Jimmy', '2020-11-03', 45
SELECT * FROM orders
-- Output
-- The IDENTITY column of the newly inserted row is now 1.
order_id customer_name order_date total_orders
-------------------------------------------------------------------------
1 Jimmy 2020-11-03 00:00:00.000 45
-- Output
-- We can also use this command to reseed the IDENTITY column to any other value like 100 as below
DBCC CHECKIDENT('orders', RESEED, 100);
INSERT INTO orders
SELECT 'Jim', '2020-02-03', 4
SELECT * FROM orders
-- Since we have reseeded the IDENTITY column to 100 the value of IDENTITY column of newly inserted row in 101
order_id customer_name order_date total_orders
-----------------------------------------------------------------------
101 Jim 2020-02-03 00:00:00.000 4
1 Jimmy 2020-11-03 00:00:00.000 45And that’s how to reset an IDENTITY column value In SQL Server!
Up Next:
Read How to display the tables containing particular strings in SQL
