MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED

Suppose you need to delete all data from the table and reset IDENTITY count. We have 2 options. One goes for TRUNCATE TABLE that resets IDENTITY (altogether it wipes out all table data). The second goes to DBCC CHECKIDENT RESEED.

It is good that DBCC CHECKIDENT RESEED feature exists, but I use TRUNCATE TABLE. DBCC CHECKIDENT RESEED has one disadvantage: if there were rows in the table before deletion, it will give 1 for IDENTITY field after the next insert, else - 0, therefore it makes it counter-productive to use this construction in the build scripts.

Related Posts:

• Why app roles are bad in MSSQL

I have read on another post that you can call DBCC CHECIDENT like this:

DBCC CHECIDENT ('table', RESEED, 0)
DBCC CHECIDENT ('table', RESEED)

And that this solves the problem.

Also, a lot of tables have foreign keys. In which case TRUNCATE TABLE results in the error message:

"Cannot truncate table 'TableWithForeignKey' because it is being referenced by a FOREIGN KEY constraint."

DBCC CHECIDENT ('table', RESEED, -1)