Reset auto-incrementing column

Question

I recently added Items to an ID and the the table got messed up in the transfer process so I deleted the Items from the table. Upon reentering the data instead of the ID starting at one it now starts at 332. I would like to have the table start at one instead of 332. I've removed the data from the data so it's clear. How do I reset the ID to one. Thanks and sorry if this on here somewhere I wasn't sure how to search for this.

Answer 1

Assuming MSSQL:

DBCC CHECKIDENT('MyTable', RESEED, 0) -- One less than next ID to allocate

If you want to remove the data too you can use

TRUNCATE TABLE MyTable

but you cannot use TRUNCATE TABLE on a table referenced by a Foreign Key, or if the table is part of an indexed view, and unlike DELETE MyTable any trigger(s) on the table won't be activated.

Answer 2

you need to truncate the table

but to do so it has be empty, and no foreign keys attached to it at all

Answer 3

If you are using MS Access, delete and recreate the table

Answer 4

Set the starting identity value to 1

DBCC CHECKIDENT (tableName, RESEED, 1)

Answer 5

In SQL Server:

DBCC CHECKIDENT (myTable, RESEED, 0)

Answer 6

truncate table yourtable --will reseed