I was trying to reset the IDENTITY value of a table. The problem was I had a foreign key constraint so I couldn’t TRUNCATE the table. That would have solved my problem though.
My table had 52 records, so when deleting all of them, the identity value was the same. So when I inserted a new row, it got the IDENTITY value of 53. That was no good.
As I said before, I tried TRUNCATE but it wouldn’t work because of a foreign key constraint. I didn’t want to recreate my tables, so that’s when I found RESEED
DBCC CHECKIDENT ('dbo.TheTable', RESEED, 0);
This reset the IDENTITY field to 0, so next when I inserted a record, it got the value 1 – which is exactly what I wanted!
What a great find!