start IDENTITY values at a new seed

This can be useful if you want to create an artificial gap between your current seed and your desired next identity value.

SET IDENTITY_INSERT [myTable] OFF INSERT INTO [myTable] (id,other)     VALUES(@NewSeed,'something') SET IDENTITY_INSERT ON [myTable] ON

In this case, the id column will continue incrementing from the vaue of @NewSeed.

If you want to remove all entries in a table and start over at 1 (or a non-default seed), you can do one of two things:

DROP TABLE [myTable] CREATE TABLE [myTable] (     -- Column definition ) -- or TRUNCATE TABLE [myTable]

Note that TRUNCATE TABLE will not work in particular scenarios, e.g. if another table has a foreign key constraint pointing at the table you're trying to reset (even if there is no data representing that relationship). What you can do instead is:

DELETE [myTable] -- assuming you want to clear the data DBCC CHECKIDENT('myTable', RESEED, 0)

The final parameter is a little awkward. If you want to make sure that the next IDENTITY value that gets generated is 1, you set this value to 0 (this is the typical case). If you want to inject a gap into the IDENTITY sequence, let's say you want the next IDENTITY to be 25000, you would say:

DBCC CHECKIDENT('myTable', RESEED, 24999)

Of course, that all assumes that your increment value is 1. If your increment value is 50, you would use 24950 instead of 24999.

It's not a great idea to reseed an IDENTITY sequence *lower* than any values that currently exist in the table. You might get a little surprise when your counter gets back up that high and hits an existing value...

---

For Access, you can reset the AUTONUMBER column by compacting and repairing the database (see Article #2190). Though with certain version / JET combinations, this can cause the engine to fill gaps when you start inserting again. KB #257408 explains this to some degree, but states that it will only use numbers lower than the max that was ever in the table, if all higher numbers have been deleted (e.g. won't fill gaps). There are several documented cases out on Google where users have observed their gaps being filled in, even when higher numbers still exist in the data. So I wouldn't count on your gaps staying intact.

source: http://sqlserver2000.databases.aspfaq.com/can-i-start-identity-values-at-a-new-seed.html