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.
