Unanswered: Changing identity seed and increment after the fact
I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ....). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.
My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?
Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?
I'm Looking at the help files on DBCC checkident now. It doesn't look like it will convert any existing records in the table. If there are 100 records in the table with identity values 1 through 100 and I change the seed/increment valuse to 10000/+1, I'm asuming that the existing 100 records will maintain their current ident values?
Yes, the value will be the same even you execute the DBCC. You can save the table to a temp, delete all rows out of the existing table, change the identity seeding, and then reload the data. That will do it.