Our Training Programs

All contents are copyright of their authors.

Reset auto increment value in Sqlserver

Reset auto increment value in Sqlserver

Milu Swain

11yAsked Sep 26, 2015, 12:28 PM1.6k

I have one column in my Order table and that is auto increment.

Now I want to reset it means it should start from 1000 and next value will be 1001. How can I do.

Know the answer? Post it — somebody with the same question will find it here.

It is the same account you read, post and publish with — and you will come straight back to this page.

Pankaj Kumar Choudhary11y ago
Accepted answer
Hello ! Milu

Reseed the identity Column:

We can also reseed the identity field value. By doing so identity field values will start with a new defined value. This method checks the current identity value for the specified table in SQL Server and, if it is needed, changes the identity value. We use DBCC CHECKIDENT method to manually set a new identity value for the identity column.

/*SET NEW VALUE TO IDENTITY COLUMN*/

DBCC checkident (table_name, RESEED,1000)
Supreeth J11y ago
Hi, the below query will work for new Entries, if you want to change existing one , u have to make manually by drop Primary key & again call auto increment

DBCC CHECKIDENT ('TABLE_NAME', RESEED, 1000);
Sujeet Suman11y ago
Milu please one go through below link:

http://www.codeproject.com/Tips/264381/How-to-RESET-identity-column-in-SQL-Server
Raja T11y ago
Hi,Try below code:

DBCC CHECKIDENT ('your table name', RESEED, 1000);

Above query execute staring I'd 1001 automatically reset....
Manas Mohapatra11y ago
Try below code:

DBCC CHECKIDENT ('Person.ID', RESEED, 1000);

Here Person is table and ID is column which will increment 1 in next value. You can change as per your requirement.