SQL Server

Reset IDENTITY field in SQL Server with RESEED

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!

Leave a Reply

Your email address will not be published. Required fields are marked *