I created a database table in VB.NET 2005 Express. I defined the primary key and set it to autoincrement for each record. I deleted all the data in the table and reloaded it with revised data. The primary key did not reset back to 1 for the new data.
How can I force the Primary Key ID to reset back to 0 so the new data items start at 1 again?
you can use
TRUNCATE TABLE <TableName>
TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.
You can also use the code below to reseed your identity while retaining your current data in your table...
DBCC CHECKIDENT (MyTable, RESEED, 1)
If this reply answers your question, please visit the link above and click "Mark as Correct Answer". This improves search results, helps us build a FAQ, and credits helpful volunteers.
- Proposed as answer by Creativity Thursday, December 24, 2009 2:52 AM
Hey All:I believe that Leon meant:DBCC CHECKIDENT(MyTableName, RESEED, 0).Step-by-Step:1- Delete all of the data in your table2- Type: DBCC CHECKIDENT(YourTableName, RESEED, 0)NB:To test the result,1- Insert New Data in the table2- use SELECT Statement and examine the value of the PRIMARY KEY (Assuming it's an Integer, the value should be 1)"May The Force Be With You"
- Proposed as answer by Creativity Thursday, December 24, 2009 3:05 AM
Where I can run these steps listed below:Step-by-Step:
1- Delete all of the data in your table
2- Type: DBCC CHECKIDENT(YourTableName, RESEED, 0)
To test the result,
1- Insert New Data in the table
2- use SELECT Statement and examine the value of the PRIMARY KEY (Assuming it's an Integer, the value should be 1)
I'm not sure if I followed you steps correctly Usha, but it didn't work for me in VisualWebExpress 2010.
What I did instead was to go to data explorer and open the table definition. From there I selected and right clicked my primary key column (or identity), and choose delete column (Making sure I recorded the original name and type.). I then right clicked the table and choose insert column. Then I named it what it should be, and set it's type (without nulls). I then right clicked the column and set it to primary key (in my case). Then I went down to column properties and set the identity specification to (is identity)=yes and saved. That set my key back in sequence with out any skips.
Not sure if this is safe for everyone but it did the job for me. I guess a backup would be a safe approach.
I posted this because that's what I think FreddieCode was asking in the first place, and my search brought this up.