How to use unused value in autoincreement column RRS feed

  • Question

  • Hi

    I have one list having data from 10 to 100...some records were missed in between,,when I manually enter the missed get save in list..but when next row is added ,the next increement id is last updated one...ex..6 and 7 was missed in between,,I manually enter the 6..but next time when row is added,,the autoincreement column store value is101..not 7...means it increement value from last record not from the one manually how can we do it?

    Abhinav Agarwal

    Tuesday, June 18, 2019 5:43 AM

All replies

  • Hi Abhinav,

    How did you initially set auto-increment for the specific table where this issue exists? I do not have an answer for how to work with the differences between the auto increment as the functionality is to guarantee a unique value but not necessarily a sequential series. Please see read below.

    If the table was created using the following CREATE TABLE (Transact-SQL) IDENTITY (Property) (link), then you cannot override the seed+increment value which this functionality has been counting from.

    With regard to the missing values, please the Remarks section (link) as there is a very specific point about consecutive values:

    "Consecutive values within a transaction - A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level."

    "Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated."

    Wednesday, June 19, 2019 9:43 PM