SQL Server | Resetting Identity Column

Identity column’s are used when you want to have a serial number for each row that get inserted into the table. In other word, it will create a numeric sequence. We can make a column identity while creating the table. Check this simple query which will create an Employee table.

CREATE TABLE EMPLOYEE(EmpID int identity(1, 1), EmpName varchar(20))

The above statement will create a table named EMPLOYEE with two columns, EmpID and EmpName. EmpID is an identity column, the 1st record will have the EmpID as 1 and all subsequent records will have a value incremented by 1. So if you give identity(1,2), each record will have the EmpID as an increment of 2

At some instant we might require the identity value of the insert statement.

SELECT @@IDENTITY

If you want to insert a value into an identity column you can use the SET IDENTITY_INSERT statement.

Once you delete all the data from the table, the identity column won’t get reset to the initial value. For doing this we must use the following statement.

DBCC CHECKIDENT('Employee', RESEED, 0)

Another approach is to Truncate. This will delete all data as well as reset the Identity column.

TRUNCATE TABLE Employee