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

It seems ridiculous that you can't set/reset an identity column with a single command to cover both cases of whether or not the table has had records inserted. I couldn't understand the behaviour I was experiencing until I stumbled across the question above on SO!

My solution - ugly but works - is to explicitly check the sys.identity_columns.last_value table which tells you whether or not the table has had records inserted, and call the appropriate DBCC CHECKIDENT command in each case:

DECLARE @last_value INT = CONVERT(INT, (SELECT last_value FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'MyTable'));
IF @last_value IS NULL
    BEGIN
        -- Table newly created and no rows inserted yet; start the IDs off from 1
        DBCC CHECKIDENT ('MyTable', RESEED, 1);
    END
ELSE
    BEGIN
        -- Table has rows; ensure the IDs continue from the last ID used
        DECLARE @lastValUsed INT = (SELECT ISNULL(MAX(ID),0) FROM MyTable);
        DBCC CHECKIDENT ('MyTable', RESEED, @lastValUsed);
    END

Answer 2

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