1. Background
The purpose
of this article is to describe how to reset the identity column of a table in
SQL Server.
There are two ways of resetting the value of identity column in SQL
Server:
·
TRUNCATE TABLE
TRUNCATE TABLE
·
DBCC CHECKIDENT WITH RESEED
DBCC CHECKIDENT WITH RESEED
2. Steps to
reset the Identity column:
i. Reset Identity with TRUNCATE–TABLE:
TRUNCATE TABLE quickly
deletes all records in a table by deallocating the data pages used by the
table. This reduces the resource overhead of logging the deletions, as well as
the number of locks acquired. Hence SQL Server does not maintain identity
values of a table after truncating the table.
Below
is the example:
is the example:
·
Table Schema:
CREATE TABLE
[dbo].[City](
Table Schema:
CREATE TABLE
[dbo].[City](
[ID] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](250) NULL
) ON
[PRIMARY]
[PRIMARY]
GO
·
Data Insertion:
Data Insertion:
INSERT INTO City
VALUES(‘Bhopal’),(‘Indore’),(‘Mumbai’),
(‘Hyderabad’),(‘Jabalpur’ )
·
See the data in table:
SELECT * FROM City
See the data in table:
SELECT * FROM City
·
Delete the data using DELETE command:
DELETE FROM
City
all the five records deleted.
Delete the data using DELETE command:
DELETE FROM
City
all the five records deleted.
·
Re- Insert the same data:
INSERT INTO
City
Re- Insert the same data:
INSERT INTO
City
VALUES(‘Bhopal’),(‘Indore’),(‘Mumbai’),
(‘Hyderabad’),(‘Jabalpur’ )
·
SELECT the data:
SELECT * FROM City
Here we can see Identity value increasing after deletion.
SELECT the data:
SELECT * FROM City
Here we can see Identity value increasing after deletion.
·
Now TRUNCATE the table:
TRUNCATE TABLE
City
Now TRUNCATE the table:
TRUNCATE TABLE
City
·
Now Re-insert the same data:
Now Re-insert the same data:
·
Now check the data:
Now check the data:
ii. Reset Identity by DBCC CHECKIDENT WITH RESEED option
In case, you
can’t use TRUNCATE command then you can go with DELETE command and DBCC
CKECLIDENT with RESEED option.
·
DELETE data from table
DELETE FROM
City
DELETE data from table
DELETE FROM
City
·
Now use DBCC CHECKIDENT with RESEED option to reset the
identity:
DBCC CHECKIDENT (‘City’, RESEED, 0)
Now use DBCC CHECKIDENT with RESEED option to reset the
identity:
DBCC CHECKIDENT (‘City’, RESEED, 0)
- Now insert the data into table and see the result
Now
Identity has been reset.
3. Conclusion
By using TRUNCATE
TABLE and DBCC CHECKIDENT with RESEED, we can reset the value
of Identity
column of a table in SQL Server.
TABLE and DBCC CHECKIDENT with RESEED, we can reset the value
of Identity
column of a table in SQL Server.