Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added them again. Now my id's go from 68 -> 92 and this crashes my code.

How do I reset the IDENTITY value?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

You can reset the identity value by

DBCC CHECKIDENT('tableName', RESEED, 0)

So next time you insert into TableName, the identity value inserted will be 1.

When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case.

Now when you truncate the table, it will reset the Identity value to its original Seed value of the table.

Refer to : SQL SERVER – DELETE, TRUNCATE and RESEED Identity for a detailed example and some good explanation of Difference between Truncate and Delete

share|improve this answer

Kin has shown you how you can reset the IDENTITY value, but outside of a development environment when you're really removing all of the data, why do you need to do this?

I hope you are not intending to maintain a contiguous sequence of IDENTITY values when you are in production. And I hope you aren't really writing your code to hard-code the IDENTITY values. If these are meaningful ID values then you should stop using the IDENTITY property.

There are a few things that will prevent this from happening:

  • if an IDENTITY value is assigned during a transaction, and the transaction is rolled back, the value isn't "given back" and the next value will be the one never used + 1.
  • if a row is later deleted, the IDENTITY never comes back to fill in the gaps.
  • there is an active bug in SQL Server 2012 that won't be fixed until SQL Server 2014 (unless you use an undocumented and very expensive trace flag) whereby a restart will seem to discard up to 1000 values from your IDENTITY column. The bug on Connect suggests that this is restricted to failover events involving Availability Groups but I can assure you the bug is much more broad than that.

In short, if you care about gaps or wish to give these values specific meaning, stop using IDENTITY. Drop and re-create the table and when you need to delete the values and re-populate, either perform an update, or perform an insert with hard-coded values for that column.

As an aside, primary key and identity are not the same thing. An identity column is not a primary key unless you explicitly define it as such, and you can certainly have a primary key that is not an identity column.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.