identity columns - keeping values in sync

  • Hi all

    A programmer recently asked me if I can "reset" an identity column on a specific table to start from a specific value. The case is where we have a reference data table with values:

    0

    1

    2

    3

    if we insert a new row SQLServer gives us 8 instead on 4 as previous data was inserted then removed.

    I have tried to profile the code sql*server runs during a row insertion over an identity PK enabled table and have failed to work out what sqlserver is doing behind the scenes to allow us to maniuplate the identity (short from removing it and then re-enabling it which does work).

    Any ideas on best practice on re-set the identity?

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Take a look at DBCC CheckIdent with the reseed option. Does just what you need I think.

    Andy

  • Right on the money Andy.

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply