Reset Identity column in SQL Server and Mysql

This is one of those simple tip posts that may seem obvious and taken for granted by those of us who have been working with SQL Server for a while now but maybe a newbie or two out there will find this helpful.

Every so often (just this morning!) I find myself resetting an identity column value back to 0after I've deleted all the existing records so the table gets a fresh start at primary key 1. Yes, I know all about primary keys not changing and how the value in the primary key doesn't matter and so on. Sometimes I just like the primary keys starting at 1.

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Customer', RESEED, 0)

This is how we can reset the identity column in SQL Server....

For Mysql, we have a bit different command for resetting identity column

ALTER TABLE tablename AUTO_INCREMENT=0

However, if you have existing rows, the AUTO_INCREMENT value will only reset to one higher than the maximum current ID. For example, if you have a table with the following entries…

ID	First name
3	Peter
6	Paul
7	Mary

… and the AUTO_INCREMENT count is set at 60, the statement to reset the AUTO_INCREMENT count to 0 will only reset it to 8. Only if there are no rows remaining will it reset to 0.