Post reply

Changing the auto-number/incremented value (not setting it back to 1)

  • March 8, 2012 at 11:48 am

    #258374

    I have a table with 39+ million rows. Earlier today a developer who "plays" around with this table mistakenly inserted 40,000+ rows (bringing the total up to 39,660,501) and then removed them, leaving 39,167,687 rows in the table.

    He needs to insert the proper records back into the table but the auto-incremented number will now pick up at 39,660,502 and not 39,167,688

    Is there a way to reseed it to start at the next number he wants it at? Would this work (considering I do not want to affect the prior records?)DBCC CHECKIDENT (MyTable, RESEED, 39167688)

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • March 8, 2012 at 12:06 pm

    #1456740

    set the value to 39167687

    the next value inserted will be 39167688.

    proof of concept:

    CREATE TABLE MyTable(ID int identity(1,1) not null primary key,someval varchar(30))

    insert into MyTable SELECT ('normal')

    insert into MyTable SELECT ('normal again')

    --next id should be 3 for normal people

    select * from MyTable

    BEGIN TRAN

    insert into MyTable SELECT top 40000('whoops') from sys.columns x cross join sys.columns y

    ROLLBACK TRAN --40K identities used up for nuthin! fix it!

    select * from MyTable

    DBCC CHECKIDENT (MyTable, RESEED, 39167688)

    --is this 3?

    insert into MyTable SELECT ('is this 39167688?')

    select * from MyTable --NO it's 39167689!!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 8, 2012 at 12:08 pm

    #1456742

    Thanks Lowell:-)

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • March 8, 2012 at 12:27 pm

    #1456748

    Don't let your developer play around in the live database!

    Note that RESEEDing the table can cause problems because of duplicate values if not done carefully. In the vast majority of cases, this number is essentially meaningless, and there's no real value in forcing contiguous values. I would just leave it alone.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • March 8, 2012 at 3:00 pm

    #1456834

    In fact, you will always have holes in the sequence. For example if you do a rollback after an insert, the value will not be reset, even though the row that used to be identified by this value is not in the table any more.


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

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

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