Tip: How to reset the identity column in a sql server database

Tip: How to reset the identity column in a sql server database

  
Hi, I had to reset the identity column to 0 again for one of my applications. This was necessary because one of the other attributes of the entity somewhat depended on the identifier (probably that should not have the case at all). This can be done with the following command in the query screen:

dbcc checkident ('<table_name>', reseed, 0)

If you only want to know what the current identifier value is of the latest created row (still existing or not), you can use:

dbcc checkident ('<table_name>')

Cheers, Annemarie
Nice to know.

Although probably one of the golden rules related to a primary key has been violated.

Keep in mind that doing a reseed may result in broken references. Although SQL Server (since 2000) supports cascade updates, it's a propery on the foreign key reference. In other words, be aware of a possible reference integrity damage.

Marco