CODEWITHSUNDEEP
sql-server-2008-r2auto-incrementidentity-column
victorio
victorio

Reputation: 6676

Reset autoincrement in Microsoft SQL Server 2008 R2

I created a primary key to be autoincrement.

How can I reset or restart the autoincrement to 1?

Upvotes: 35

Views: 79827

Answers (2)

user2309101
user2309101

Reputation: 211

I'm using SQL Server 2012 and the DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1) causes a value of 2 on the very next insert.

So for SQL Server 2012, change the 1 to 0 to get a value of 1 for your next record insert:

DBCC CHECKIDENT ("YourTableNameHere", RESEED, 0);  -- value will be 1 for the next record insert

Upvotes: 21

marc_s
marc_s

Reputation: 755541

If you use the DBCC CHECKIDENT command:

 DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);

But use with CAUTION! - this will just reset the IDENTITY to 1 - so your next inserts will get values 1, then 2, and then 3 --> and you'll have a clash with your pre-existing value of 3 here!

IDENTITY just dishes out numbers in consecutive order - it does NOT in any way make sure there are no conflicts! If you already have values - do not reseed back to a lower value!

Upvotes: 63

Related Questions