Reset Identity Column Counter.

Is there a way to delete all items from a table that has an identity column and to reset the counter for all new insertions so that they begin at '1' again?

TRUNCATE command resets identity counter.

http://msdn2.microsoft.com/en-us/library/ms177570.aspx

It's also minimally logged and will run faster than the DELETE statement

|||

If TRUNCATE TABLE MyTable does not work for you, let us know.

There are other options if necessary.

|||Although I am able to delete all rows from the table (using 'delete from sometable'), attempting to truncate (using 'truncate table sometable') returns the error: 'Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'sometable' because it is being referenced by a FOREIGN KEY constraint.

The other table that references 'sometable' is also empty and was successfully truncated. It is also the only table that shows up in the dependencies dialog.

|||

A FK needs to be disabled (or removed) before executing the TRUNCATE command (this is because, in part, of the minimal logging that allows TRUNCATE to operate so quickly)|||

See http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1258966&SiteID=17&pageid=0#1258966