General discussion


Resetting the identity seed

By MaryWeilage Editor ·
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:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Reset the seed to 1

by acperkins In reply to Resetting the identity se ...

If you want to reset the seed to 1, I believe it should be:

Collapse -


by camainc In reply to Resetting the identity se ...

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?

Collapse -


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,

Collapse -

Resetting to 1

by Jeff_D_Programmer In reply to Resetting the identity se ...

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

Collapse -

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.

Related Discussions

Related Forums