0

Create a table in my bank, in the table that contains an IDENTITY column, which was enabled in the column settings (DESIGN).

However, I am encountering a problem in the numerical sequence.

The sequence is skipping the numbers.

Example:

1,2,3,4,5,6,1001,1002,1003,1004,2001,2002.

I am having billion intervals between different numbers.

Could someone help me with a situation?

PS: Sorry for any grammatical errors in the English language.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
G Stick
  • 49
  • 1
  • 2
  • 5
  • Rows might get deleted for missing sequence. – Yogesh Sharma Apr 13 '20 at 18:00
  • 2
    Since such identity values are literally only for guaranteeing row uniqueness, why do you care? – Honeyboy Wilson Apr 13 '20 at 18:00
  • There's no need for that. Primary keys are internal identifiers that ensure uniqueness of the rows. They are not supposed to be sexy or nice looking. If you want "serial" numbers, then you probably want to expose them to the outside world. That's not their purpose. Use another *secondary* unique column for that. – The Impaler Apr 13 '20 at 18:00
  • @HoneyboyWilson identity columns do NOT guarantee uniqueness - that is fully documented. A PK or unique constraint guarantees that. – SMor Apr 13 '20 at 18:13
  • @HoneyboyWilson, I don’t use them just as a PK, but as an order table ID for example ... So I have to follow a correct sequence of numbers ... – G Stick Apr 13 '20 at 18:17
  • I've never used an identity column other than in the context of a primary key or unique constraint. But I see your point that it's the primary key or unique constraint that ultimately guarantees uniqueness. – Honeyboy Wilson Apr 13 '20 at 18:20
  • If you have to follow a correct sequence of numbers then I don't think an identity column is going to be what you want. Additionally, I think auto-generating such a correct sequence is going to prove to be more than trivial. – Honeyboy Wilson Apr 13 '20 at 18:22
  • per the dupe you can use `ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF` to eliminate one common source of gaps - but this does not guarantee a gapless sequence - it has always been the case that a rolled back insert will leave gaps with `IDENTITY` (and of course deletes can too) – Martin Smith Apr 13 '20 at 18:24
  • @HoneyboyWilson Do you know of any other way in which I can make this insertion with a sequence of correct numbers? Because my insertion in the bank occurs through external software – G Stick Apr 13 '20 at 18:26

0 Answers0