Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Unanswered: Changing identity seed and increment after the fact

    Hi all,

    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?

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Changing identity seed and increment after the fact

    You can use DBCC Checkident to reseed the seeding of the identity column.

  3. #3
    Join Date
    Feb 2003
    Posts
    41
    Thanks,

    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?

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    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.

  5. #5
    Join Date
    Feb 2003
    Posts
    41
    I think that will work. Thanks again for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •