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:

 T-SQL | 
 copy code |
?
 
1
CREATE TABLE ADMIN.DocumentType_List
2
(
3
	DocumentType_ID INT IDENTITY(0,1) NOT NULL,
4
	DocumentType NVARCHAR(250) NOT NULL,
5
	Description_ NVARCHAR(250) NOT NULL
6
CONSTRAINT PK_DocumentType PRIMARY KEY (DocumentType_ID)
7
)
8

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:

 T-SQL | 
 copy code |
?
 
1
2
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