Identity Columns

Question

I've an identity column which has multiple deletes now it is not working because it reaches the max limit of the INT data type, how can I insert records in the place of deleted ones without truncating all the data?

Answer 1

You cannot "recycle" unused IDENTITY values - if you've reached the end of the INT data range, you need to change your ID column to BIGINT.

ALTER TABLE dbo.YourTable
  ALTER COLUMN YourIDColumn BIGINT

The IDENTITY property will be preserved - now you have a lot of additional ID values available for the next couple months/years to come!