Reset Identity Field in MS-SQL

Tuesday, April 15, 2008

There are a lot of occasions when you would need to reset an 'Identity' field in the database table. For doing this in MS-SQL, you just need to execute this statement:

DBCC CHECKIDENT ('', RESEED)

This statement will RESEED the identity field to current maximum value in the column. You may also give a custom reseed value to the column:

DBCC CHECKIDENT ('', RESEED, )

Specify an integer number in place of the new_value. For example, to reset the identity column in 'tblA' to the value 20, use:

DBCC CHECKIDENT ('tblA', RESEED, 20)