Reset Identity Seed

Sometimes the need arises to reseed the identity row within an MS-SQL table. One might want to do this because records have been deleted at the end of the table, or a delete *¬†instead of a truncate has been performed on the table (which doesn’t reseed the identity row back to 0).

 

Within SQL Server, there is a built in function called DBCC CHECKIDENT, and it allows you to reseed the identity row to start at a specified number. Lets say that a table (lets call it TableA) has rows in it ranging from row 1 through 100, and for some reason rows 90 through 100 need to be deleted. Clearance is received from the auditors that these records can be deleted, and voila, a few minutes later 10 rows have been removed from the table. As time goes by an application inserts new rows into the table, however when the table is inspected you notice that the new rows have been inserted with identity rows starting at 101 upwards, and that the identity didn’t continue from 90 as expected. Because of the way MS-SQL handles Row IDs, it remembers the last Identity Row number per table, and just continues from that number forward when new data is inserted into that particular table, regardless if some or all of the data has been deleted from it.

 

To correct this, you need to determine the row id that you would like new rows to start at (in this case it’s 90), subtract one from it (making it 89), and then you need to run the following code:

 

DBCC CHECKIDENT (TableA, reseed, 89).

 

This will tell SQL that the last identity record for the specified table is 89, and so SQL will add one (1) to this value when inserting new data into the table. Of course the identity rows don’t need to start off exactly from the previous row. If you want the identity rows to start at 150, then the same code as above can be run, however the 89 would need to be replaced by 149.

 

There are a few exceptions to the use of the code, so for more information on these and other uses please visit the MSDN website. Thanks to Pinal Dave for the reference to this piece of code.

 

God bless.

Colin