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

3 Answers 3

up vote 13 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 will never be fixed (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

If you only need to eliminate the last rows which have not followed the incremental value of an identity field, there is an easy and safe way:

  1. first delete the last record(s) which have 'jumped'
  2. change the data type of your Identity Field (from int to bigint or vice versa)
  3. save the table
  4. add a new record, and check it assigns the number of the highest value + 1
  5. Replace the data type of your Identity Field, as convenient for your needs

and you are done.

share|improve this answer
2  
"Save the table" is not a meaningful statement for SQL Server. Changing data types may be trivial with less than 100 rows but could prove very expensive for large tables. –  Michael Green Mar 29 at 5:10
1  
I assume you mean in SSMS. This will create a new table, copŷ all rows into it, drop the old table and rename the new one. And you need to do this twice to toggle the datatype back. It is using a sledgehammer to crack a nut. –  Martin Smith Mar 29 at 11:50

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.