Reset autoincrement in Microsoft SQL Server 2008 R2

Question

I created a primary key to be autoincrement.

I added two rows: ID=1, ID=2
I deleted these two rows.
I added a new row, but the new row's ID was: ID=3

How can I reset or restart the autoincrement to 1?

score 63 · Accepted Answer · answered Dec 13 '12 at 10:43

63

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!

answered Dec 13 '12 at 10:43

marc_s

732,580
175
1,330
1,459

I forgot to tell, I'm using Management Studio, so I want to reseed it with the Management Studio – victorio Dec 13 '12 at 10:44
3

@victorio: just open a new query window and execute this T-SQL command! – marc_s Dec 13 '12 at 10:44
2

I am using SQL server 2008 and to reset to value 1 I have to put 0 on the last parameter, then: DBCC CHECKIDENT ("YourTableNameHere", RESEED, 0); – Marcelo Machado Jan 17 '18 at 16:02

score 21 · Answer 2 · edited Apr 14 '14 at 16:53

21

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

edited Apr 14 '14 at 16:53

Sk8erPeter

6,899
9
48
67

answered Apr 04 '14 at 23:16

user2309101

211
2
2

Reset autoincrement in Microsoft SQL Server 2008 R2

2 Answers2

Linked