Prevent gaps while using Identity/sequence object in sql server

Question

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is potential drawback of using Identity or sequence object; but is there any work around? I am open to using some other technique to prevent gaps.

Here is the table structure and sample code:

CREATE SEQUENCE Service_Ticket_Seq
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 100
 CYCLE;

 CREATE TABLE Meats
(ticket_seq INTEGER NOT NULL primary key default (NEXT VALUE FOR Service_Ticket_Seq) ,
 meat_type VARCHAR(15) NOT NULL)

Answer 1

In order to create auto numbers without gaps we can use QUEUE object of MS SQL Server.

So the idea is that Queue supports concurrency and roll back of transactions. We use SEQUENCE object to generate continuous set of numbers and send them to QUEUE. After that we can receive them back from that Queue concurrently and if one of client's requests fails then our auto number stays in queue for the next available client.

So this way we achieve concurrency and unique stream of auto-numbers without gaps.

The only potential issue is that sometimes our auto numbers are going to be in the wrong time order, but it should be ok.