Discussion Locked

Resetting the identity seed

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

5 replies

by acperkins

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

by camainc

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?

by artful

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

by jeff_d_programmer

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.

by acperkins

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.

This discussion is closed to new replies.