Dbcc Checkident Sets Identity to 0

DBCC CHECKIDENT Sets Identity to 0

As you pointed out in your question it is a documented behavior. I still find it strange though. I use to repopulate the test database and even though I do not rely on the values of identity fields it was a bit of annoying to have different values when populating the database for the first time from scratch and after removing all data and populating again.

A possible solution is to use truncate to clean the table instead of delete. But then you need to drop all the constraints and recreate them afterwards

In that way it always behaves as a newly created table and there is no need to call DBCC CHECKIDENT. The first identity value will be the one specified in the table definition and it will be the same no matter if you insert the data for the first time or for the N-th

Reset identity seed after deleting records in SQL Server

The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Example:

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

It was not supported in previous versions of the Azure SQL Database but is supported now.

---

Thanks to Solomon Rutzky the docs for the command are now fixed.

Reseed identity seed to always start with 1 - practical code

You have 2 options, essentially.

You can go the way you suggested and insert dummy data after creation and then delete it, in which case DBCC CHECKIDENT ('TableName', RESEED, 0); will always work.

You can also refrain from deleting the rows beforehand and do something like this:

IF NOT EXISTS (SELECT 1 FROM TableName)
    BEGIN
        DBCC CHECKIDENT ('TableName', RESEED, 1);
    END
ELSE
    BEGIN
        DELETE * FROM TableName;
        DBCC CHECKIDENT ('TableName', RESEED, 0);
    END
GO

DBCC CHECKIDENT RESEED -- is new value required?

As it is stated in MSDN, it is fairly enough to use just:

 DBCC CHECKIDENT('tablename', RESEED)  

most of the time, however there are these two conditions where it will not work:

• The current identity value is larger than the maximum value in the table.
• All rows are deleted from the table.

in which you have to go with they way that you mentioned (select max(id) and the rest), so why bother in the first place? :)

Weird behavior of DBCC CHECKIDENT with RESEED in a rolled-back transaction

The only real question here is why DBCC CHECKIDENT ... RESEED can be rolled back. With IDENTITY INSERT or not inserting into a table with an IDENTITY column will increment the current identity value without blocking other sessions' ability to generate new IDENTITY values. To do this, the modification of the current IDENTITY value must not be enlisted in the session's transaction.

DBCC CHECKIDENT is effectively a DDL statement, like ALTER TABLE. It requires an exclusive metadata lock on the object, and therefore can be committed or rolled back. EG

BEGIN TRAN;
DBCC CHECKIDENT('__Test', RESEED, 100);

select *
from sys.dm_tran_locks
where request_session_id = @@spid 

ROLLBACK TRAN;

Will show Sch-M locks on the target table.

---

Related Topics