Dushyanth Jyothi

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

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

Tested only on MS SERVER 2008

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.

Further Reading/Details:http://msdn.microsoft.com/en-us/library/ms176057(SQL.90).aspx