MS SQL Server – Resetting/Changing the identity[auto-increment number] column value of a table

Test Environment: MS Server 2008

DBCC CHECKIDENT checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column

Table to alter: 

CREATE TABLE Admin.DocumentType_List
(
	DocumentType_ID INT IDENTITY(0,1) NOT NULL,
	DocumentType NVARCHAR(250) NOT NULL,
	Description_ NVARCHAR(250) NOT NULL
CONSTRAINT PK_DocumentType PRIMARY KEY (DocumentType_ID)
)

In the above code ‘DocumentType_ID’ column has declared as Identity, having auto increment value by 1 starting with 0. This means the first row inserted will have ‘DocumentType_ID’ value of 1. then 2,3,so on..

In future if we want to reset this value, for what so ever reason, we can use T-SQL:

DBCC CHECKIDENT('Admin.DocumentType_List', RESEED, 0)

This code will reset table Identity back to 0.