Hi All,
I have a LOOKUP table where the ID starts from 500.all the status in our project is hardcoded so from 500 the data is there.
My question is that how to set the identity when it reaches 499 automatically it should skip the existing values and startr from the value it ends, and to fill the missing identities.
Thanks & Regards,
Kiran Murali
Identity property does not lend itself to the type of manipulation that you are describing. It is supposed to be a monotonically increasing/decreasing value. DBCC CHECKIDENT allows you to change the seed, but it is not really meant for the scenario you are describing.
My recommendation would be that you not use identity property. Instead, add logic in your code to assign the appropriate value of Id based on existing values.
Set the automatically assigned ID to run from 10,000 using DBCC CHECKIDENT - so that it does not collide with your 500, 501, ... existing values.
I assume 10,000 is big enough to avoid your existing values, if not then start at a higher number
Note that IDENTITY numbers are NOT guaranteed to be contiguous - there will be gaps - so I don't see any benefit in trying to fill the numbers below 500.
Thanks..