General discussion

  • Creator
    Topic
  • #2274462

    Resetting the identity seed

    Locked

    by maryweilage ·

    This week’s SQL Server e-newsletter explains that, regarding identity columns, there seem to be two very distinct camps. This tip is particularly aimed to the second camp in which one object is pretty much the same as another of the same domain.

    Regarding identity columns, which camp are you in? Even if you’re in the first camp, did you still find this tip interesting?

    If you aren’t subscribed to the free SQL Server e-newsletter, click the following link to automatically sign up:
    http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

All Comments

  • Author
    Replies
    • #2720169

      Reset the seed to 1

      by acperkins ·

      In reply to Resetting the identity seed

      If you want to reset the seed to 1, I believe it should be:
      DBCC CHECKIDENT (MyTable, RESEED, 0)

    • #2700208

      Why not TRUNCATE TABLE?

      by camainc ·

      In reply to Resetting the identity seed

      This is the method I’ve been using for quite some time, and it works like a charm. Is there some reason I shouldn’t use it?

      • #2700060

        Re: Why Not TRUNCATE TABLE?

        by artful ·

        In reply to Why not TRUNCATE TABLE?

        If you want to nuke all the test rows, TRUNCATE TABLE works fine. However, if you want to remove less than all the rows, TRUNCATE TABLE won’t work.

        Since you can do it both ways in the event that you want to nuke all the rows, I tried to offer a solution that would handle both situations.

        Incidentally (and not that I approve of this), but I have seen this used in a database that I worked on where “system data” was assumed to have a PK less than 1000, and “customer data” began with PK 1001. The database had over 400 tables, and its designers didn’t want to increase that number to 600+ just to accommodate the system data. So the way it worked was, we added all the system data and then reseeded the tables’ PKs. I’m not a big fan of this approach, but sometimes I don’t get to make all the decisions 🙂

        Thanks for responding,
        Arthur

    • #2700598

      Resetting to 1

      by jeff_d_programmer ·

      In reply to Resetting the identity seed

      When removing all the rows from a table, it’s easier just to run “Truncate Table “. This will remove all rows and reset the identity to whatever the original seed was.

      • #2700392

        TRUNCATE TABLE not always an option

        by acperkins ·

        In reply to Resetting to 1

        There are two problems with using TRUNCATE TABLE:
        1. Permissions. It requires you to belong to the db_owner or db_ddladmin database roles. (That is if you are not in the sysadmin role already)

        2. Foreign key constraint. If the table is referenced by a foreign key you are out of luck.

        So this will usually exclude its use from anything, but ad-hoc processes.

Viewing 2 reply threads