Should go like this: 0,1,2,3,4,5 ... + n And not like this: 0,2,3,5,6,7 ... + n enter image description here

enter image description here

  • one
    quite funny) can you delete intermediate records? - tCode
  • @tCode I do not remember. Now I start adding after 29, then output it like this: 30, 34. Could this be due to deletion? But, logically, even after removal should be 30.31. - Andrew_Romanuk
  • four
    No matter how he considers it is impossible to expect a non-breaking primary key and nothing should depend on it. Consequently, gaps should not worry. And how do you get it by the way the identity field or maybe a sequence (which is cached and because of this there may be gaps) - Mike
  • And after deleting the records, no one will use the previously used numbers again - Mike
  • If the lines are clearly not deleted, and "holes" in the ID range are present, then there may have been attempts to insert into the transaction that was eventually rolled out. - Kladivo

2 answers 2

Must go like this: 0,1,2,3,4,5

If this is a primary key - auto-increment, then it MUST NOT. The auto increment should generate the next value, which will be greater than the previous one generated. Everything.

The primary key is not for you at all. It is for organizing the connection of tables and the work of the subsystem to maintain the integrity and consistency of data.

Do not try to assign ONE field to ONE field. Also unrelated. It won't end well.

If you need a field with continuous numbering for user purposes, create a separate field for this and fill it in software (trigger, sequence, etc.).

    When specifying a primary key constraint for the Database Engine component table, data is unique by automatically creating a unique index for the primary key columns. This index also provides quick access to data when using a primary key in queries. If a primary key constraint is specified for more than one column, then the values ​​can be duplicated within the same column, but each combination of the values ​​of all the columns in the definition of the primary key constraint must be unique.

    This means that you cannot add a record, delete a record, and then add a new record so that the id does not change, i.e. if we already used Id = 7, but deleted the record, then adding a new id will automatically be equal to 8 or more if the next 7 keys were used.

    Note: Do not expect that all keys in the table will go in order if at least one record has been deleted from the table. This is just an index field for quick access.

    Source: msdn