Monday, August 29, 2011

How to Reseed Indentyty In SQL Server 2008

When we delete a table in sql server db all values are deleted but the Seed set to the last identity.Means if there was 50 fields in that table and and the last identity was 50 with identity increment with 1 and here we delete the table but if we insert again in this table the identity will be 51 instead on 1. If you wants the identity to be one then you need to reseed the identity to 0 so the next identity will be 1.
 Note : This is the main difference between the TRUNCATE table and DELETE table. TRUNCATE reset the table definition after deleting all fields where DELETE only remove table values

Here is the example to reseed the tbl_SubModel seed to 0

DBCC CHECKIDENT ("dbo.tbl_SubModel", RESEED, 0);