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.
