Post reply

Can I reseed an identity column with data still in the column/table

  • January 28, 2009 at 10:20 am

    #77179

    Say I have a tableA with the following columns:

    ID (PK, int, identity, not null)

    name (varchar(50), not null)

    address (varchar(50), not null)

    I originally had the identity seed set at 0, and I have 10 rows of columns populated. So, ID columnn has values from 1 to 10. I now want to start the auto increment identity counter to start at 20. Can I reseed the ID identity column in tableA if the table already has data in it? If so, are there any performance ramifications?

    Can I simply use this command:

    dbcc checkident('tableA',reseed,19)

    If so, then would the next row I add have an ID value of 20?

  • January 28, 2009 at 10:26 am

    #933973

    The answer is yes

    CREATE TABLE tmp

    (col1 INT IDENTITY,Col2 INT)

    INSERT INTO tmp VALUES(1)

    SELECT * FROM tmp

    INSERT INTO tmp VALUES(1)

    DBCC CHECKIDENT(tmp,reseed,19)

    SELECT * FROM tmp

    the question is why do you want to do that?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • January 28, 2009 at 10:41 am

    #933991

    Christopher,

    The reason for doing this is because we are migrating trouble tickets from an old help desk ticketing system to a new one. In doing so, I am going to pre-populate (insert into/values) over 13,000 old tickets, then perform update/set where the old and new ticket numbers match. However, since we are still going to be live in the old system while running my migration queries, I want to reseed just after migrating 13,000 tickets to allow a buffer for new tickets that are auto generated during the migration period. So, I would reseed to say 13,010 which allows me to have up to 10 new tickets auto generate and be able to correctly map/maintain the old ticket numbers in the new system. It's almost too difficult to explain. Ultimately, what I want to do is make it so all new tickets (in the new tracking system) start at a specific number (say 13,010). All old migrated tickets will have an ID of 13,010 or less.

  • January 28, 2009 at 2:32 pm

    #934210

    Pat_B (1/28/2009)

    Say I have a tableA with the following columns:

    ID (PK, int, identity, not null)

    name (varchar(50), not null)

    address (varchar(50), not null)

    I originally had the identity seed set at 0, and I have 10 rows of columns populated. So, ID columnn has values from 1 to 10. I now want to start the auto increment identity counter to start at 20. Can I reseed the ID identity column in tableA if the table already has data in it? If so, are there any performance ramifications?

    Can I simply use this command:

    dbcc checkident('tableA',reseed,19)

    If so, then would the next row I add have an ID value of 20?

    Why not just make a dummy table somewhere and try it? You might want to take a look at Books Online for what the next value would be after a reseed, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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