We help IT Professionals succeed at work.

Reverting auto increment to its original value in MSSQL 2008?

Hi there;

I believe that this question may be asked 1000 times, but let me ask;

I want to reverting auto increment value to its original value which starts as 1 in MSSQL 2008.

How to do?

Kind regards.
Comment
Watch Question

Database Administrator
CERTIFIED EXPERT
Commented:
You can either drop and recreate the column or do a RESEED through DBCC CHECKIDENT.

Are you sure you want to restart your auto increment value?  I would only do this if it's isn't a key in any other table and you are absolutely sure it won't cause a problem.
CERTIFIED EXPERT

Author

Commented:
The database except for table is empty, so np.

>> do a RESEED through DBCC CHECKIDENT.
How and Where is that setting?

Kind regards.
Chris MangusDatabase Administrator
CERTIFIED EXPERT
Commented:
The quickest thing would be to drop the column and recreate it.

To do a RESEED you would issue the command like this:  DBCC CHECKIDENT ("{myTable}", RESEED, 1);

This command only works if you've deleted all the rows from the table and want to reseed the identity value back to 1.
CERTIFIED EXPERT
Top Expert 2012
Commented:
>>This command only works if you've deleted all the rows from the table and want to reseed the identity value back to 1. <<
Actually it will "work" even if you have not and even if you have a row with an ID of 1.

>>The database except for table is empty, so np.<<
If there are no foreign keys you can also do:
TRUNCATE TABLE YourTableName

And this will set it back to the original seed defined in the table.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.