none
How to Reset the Primary Key Counter

    Question

  • 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?

    Thanks.

     

    Thursday, April 20, 2006 2:26 AM

All replies

  • I think you've to recreate the table or modify the table structure to reset the identity.
    Thursday, April 20, 2006 2:58 AM
  • Hi,

    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)


    HTH,
    __________________________________
    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
    Thursday, April 20, 2006 3:25 AM
  • Hey All:

    I believe that Leon meant:

    DBCC CHECKIDENT(MyTableName, RESEED, 0).

    Step-by-Step:

    1- Delete all of the data in your table

    2- Type: DBCC CHECKIDENT(YourTableName, RESEED, 0)

    NB:

    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)

    "May The Force Be With You"
    • Proposed as answer by Creativity Thursday, December 24, 2009 3:05 AM
    Thursday, December 24, 2009 3:02 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)

    NB:

    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)
    Wednesday, May 05, 2010 2:00 AM
  • HI

    No need to truncate the table. Just change the identity specifier to NO and Execute and again reset to YES and execte. This will work.

     

    • Proposed as answer by Usha R Monday, June 28, 2010 4:38 AM
    Monday, June 28, 2010 4:38 AM
  • 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.

    Monday, February 21, 2011 6:47 AM
  • Have you ever looked into SQL Profiler what SQL Server does behind the scene while  you click YES/NO via SSMS?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, February 21, 2011 7:27 AM
  • Have you ever looked into SQL Profiler what SQL Server does behind the scene while  you click YES/NO via SSMS?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, February 21, 2011 7:27 AM