0

I want to reset my auto incremented value after I use delete, If I use post after using delete the value will increment and not fill the previously existing ID, for instance if I delete ID 5 and use post to create then ID 5 will be skipped over and ID 6 will be created. I am using code first approach.

  • 1
    Possible duplicate of [Reset AutoIncrement in SQL Server after Delete](https://stackoverflow.com/questions/510121/reset-autoincrement-in-sql-server-after-delete) – Peter B Nov 30 '18 at 12:03
  • Read the following. https://dba.stackexchange.com/questions/43910/reset-identity-value This is actually something that you cannot do and it is also strongly advised not to do – rpd Nov 30 '18 at 12:10
  • 1
    That's the correct and logical behaviour. Nothing is skipped. Do you *really* want to associate a **new** record with any related table entries that have the *old* ID? What you want to implement is a serious bug – Panagiotis Kanavos Nov 30 '18 at 12:11
  • @PeterB when someone asks how to shoot himself in the foot it's best to add a warning before linking to instructions – Panagiotis Kanavos Nov 30 '18 at 12:12
  • From Chirag Bansal: *Please can you be more specific about the database. Also as I know in MySQL, the auto increment counter arranges itself.* – Panagiotis Kanavos Nov 30 '18 at 12:13
  • Possible duplicate of [Reordering Identity primary key in sql server](https://stackoverflow.com/questions/14023574/reordering-identity-primary-key-in-sql-server) – Ilyes Nov 30 '18 at 12:30

1 Answers1

0

There are 2 approaches - You can re-seed the Identity property after your Delete:

EG

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

OR (if you are deleting all rows)

You Can simply TRUNCATE the table. (assuming there are no FKs)

First option require dbo - 2nd option requires Alter Table

john McTighe
  • 1,181
  • 6
  • 8