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
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!