Deleting rows from a table with an Identity Column

If you delete all the records from a table it won’t reset the identity.

To reset the identity seed you need to use a DBCC command.

DELETE FROM myTableName
DBCC CHECKIDENT('myTableName', RESEED, 0)

// DBCC CHECKIDENT (table_name, RESEED, new_reseed_value)

Setting new_reseed_value to 0 will cause the next INSERT with an identity = 1

This T-SQL applies to MS SQL Server 2000+

Advertisements