Delete Table Rows + Reseed The Table

By Mithun Bose at January 09, 2010 16:50
Filed Under: SQL Server

During the development phase I frequently come across the need of deleting a table and resetting the IDENTITY column back to 0.

The truncate statement does reset the identity seed to its original value; however, truncate is now allowed on a table where the columns are being referenced as foreign key. You could remove the foreign key reference and truncate the table and then recreate the foreign key but that's a messy way of getting around it.  I normally use following two queries:

delete from SomeTable 
DBCC CHECKIDENT ("SomeTable", RESEED, 0)

Having said that, if your table is very large it probably will be quicker to drop the foreign key ref and then truncate and recreate the foreign key. But for smaller size table the above mentioned queries are much cleaner way of achieving this requirement.

About the author

I am a professional software developer based in London, England. I specialize in developing enterprise application and integration framework in C#, SQL Server, ASP.NET. 

My area of interests include SOA using WCF, Web, Business Intelligence and Database programming. I am a Computer Science Graduate and Microsoft Certified Professional Developer in enterprise application.