Resetting a primary key’s identity seed is something I do from time to time when testing out tables where the primary key is typically along the lines of

ID int primary key identity(1,1)

IMHO the most common way of doing this is the using the DBCC command:


This will ensure the next generated identity value is one. Personally is do this after deleting all the test data in the table:

delete from MyTableName

Thanks to TakeNote… for refreshing my memory on this (there are lot’s of other forum/blog posts on the ‘net about this same issue of course).

Update  2009-09-18

Inserting explicit values for identity columns can also be very useful. Credits to Pinalkumar Dave‘s blog for this useful information:

Use this before the SQL statements which insert values into the identity column(s) of your table:

set identity insert <table name> on

And then this statement to set things back to normal afterwards:

set identity insert <table name> off