Full Reset of Identity

  • I'm looking for a script that completely rebuilds the identity value of a table.

    We append several (hundred of)thousands of records from different sources into one overlapping table.

    Because we need to get rid of all duplicates, but keep the records that have been imported earlier we use identity column. (and delete all duplicate records having a higher ID than the lowest id for each duplicate record)

    After all duplicates have been discarded we would like to rebuild the identity from 1 to x (so the max record = max identity)

    Dropping the column and adding again is a possibility, but seems like overkill.

    Tried DBCC CHECKIDENT (

    , RESEED, 1)

    But It does not reset the identity.

    Anyone with a swift solution??


    Kindest Regards,

    Colin van Viegen

  • Why do you think it's an overkill? If you can do so, I think that is the easiest solution. Anyway, CHECKIDENT won't help you much here, as it will only reseed the next to be assigned IDENTITY value and doesn't rebuild already existing values.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • How about selecting every column apart from the identity cloumn into a temp table, inserting into the temp table using a SELECT DISTINCT, truncating the main table then inserting the contents of the temp table into the main table?

    Your 2 insert statements will have to specify every column apart from the identity column and if the table is very wide the staement may take a long time to run but I can't see another way to do it.

  • Thank guys for your feedback.

    It seems Microsoft SQL Server has no direct answer for this issue, rather than drop and add the ID column again. (just like frank suggested)

    It's also the fastest way. but seems somewhat strange to completely drop a column, and then adding the same column again. (especcially when there are almost 7 million records in this table.)


    Kindest Regards,

    Colin van Viegen

  • And to be honest I don't think there is any guarantee that you will get those ID in the correct (expected) order. To be on the safe side I would create that extracolumn empty at temp table creation time and update the values on it after the delete. That way you are sure that the record is getting the right ID

    Just my $0.02

     


    * Noel

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

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