Gail Shaw shows how to build gapless sequential values:
To be clear, I don’t think this is a good idea. The identity column works well if a sequential series of numbers are needed. If the number sequence needs to that spans tables, then the sequence object is a good replacement.
But, there will always be some requirements that insist on gap-less sequences, or insist on not using identity (probably for ‘compatibility’ reasons), so let’s see how to do it properly.
For true surrogate keys, this is a bad idea because it is both unnecessary and overkill. Where this becomes useful is cases where an auditor is expecting a proper sequence without any gaps, such as invoice numbers, check numbers, or purchase order numbers. As Gail mentions, those are uncommon scenarios.