Reset AutoNumber in SQL


  • Hi everyone,

    I recently migrate a DataBase from Microsoft Access in a replication scenario with those evil replication autonumbers to SQL Server 2008 R2

    The PK of all tables where the replication autonumbers in access.

    I want to keep the same PK with there values but I would like to know if it's possible to make the auto-number restart at beginning to fill empty "range". At the moment, it increment starting at the highest value. So if I have those PK (1, 2222, 3333, 5555) I would like that the next value is gonna be 2 instead of 5556

    Do I also have to execute a script (if it's possible) against every table of the database ?

    Thanks for the help

    Richard Martin Web Developer / BI Developer

    Saturday, June 30, 2012 6:13 PM


  • Hi Richard:

    RESEED will helps u reset the auto identity value.

    A table has 116 rows with 116 as last identity. The next record will automatically genrate 117, If you wants to start the new identity as 444 use the follwing T SQL query in query analyzer.

    DBCC CHECKIDENT (tableName, reseed, 444)

    Hopes this helps....

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    • Marked as answer by marric01 Saturday, June 30, 2012 9:56 PM
    Saturday, June 30, 2012 8:11 PM