Is there a way to leverage the identity feature to backfill a primary key for an existing very large table? What I have done in the past is script a new table from the old table adding a new identifier column (example: column AttributeID int identity(1,1) not null) then writing a t-sql script to populate the table using the rownumber from the source (old table) to manage the inserts in chunks to avoid blowing up the transaction log. Is there a better way to do this?

I realize that even if there is, the next concern will be the needed new space for this added constraint/index + providing more transaction log space.

Any advice regarding a different approach and/or resource management will be greatly appreciated.

  • 1
    Does this answer your question?
    – HandyD
    Commented Nov 4, 2021 at 21:53
  • @HandyD, I will try this on a smaller scale. I will update the thread with the results either way.
    – plditallo
    Commented Nov 5, 2021 at 21:13
  • May be I'm somewhat off-topic, but for 2 billion + rows, the AttributeID data type should probably be a bigint if I'm correct. you may also set the seed of the identity column to -2,147,483,648, but there will still be a problem when the number of rows reaches 4 billion++.
    – ggo
    Commented Nov 10, 2021 at 13:37


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.