Reseed identity column to 0 fails - current identity value is NULL

Question

I restore database and delete records in my_table using

 delete from my_table;

Then I reseed table identity column using this query:

DBCC CHECKIDENT('dbo.my_table', reseed, 0);

and the message I got is:

Checking identity information: current identity value 'NULL', current column value '0'.

But, when I check current identity using this query:

DBCC CHECKIDENT ('my_table', NORESEED); 

I got this message:

Checking identity information: current identity value 'NULL', current column value 'NULL'.

So, when I'm inserting record in table, first insert gives me an error, but if I try once more then insert succeed.

Why I can't set current identity column value to 0? I need first insert in table to be with identity value 1.

Answer 1

Instead, use:

truncate table my_table;

which will reset the identity column automatically. (Plus, it's going to be faster than deleting the rows using delete)

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Ref.: Truncate Table