1

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)
Improve this question
13
  • 5
    Why do you "want to prevent gaps in the primary key column"? That response may affect the validity of answers. Remember that a query can always use ROW_NUMBER() over (ORDER BY ID) to sequence its results without gaps. – Pieter Geerkens Sep 28 '14 at 14:57
  • 5
    "I want to prevent gaps in the primary key column" - why? The value of a primary key - especially an auto generated one - is meaningless. It's only purpose is to uniquely identify a row. And to do that job the value itself is totally meaningless. And gaps are equally meaningless. – a_horse_with_no_name Sep 28 '14 at 15:24
  • 1
    @PieterGeerkens : I am Preparing for 461 certification and it just a thought which crossed my mind.. :) – user2438237 Sep 28 '14 at 16:26
  • 1
    The 'CYCLE' option on a sequence will cause it to loop back to the minimum once the maximum is reached, which would violate your primary key constraint. – dartonw Sep 28 '14 at 17:20
  • 2
    You're still telling us the version of management studio and not the version of SQL Server. Not that it matters much; you can't prevent gaps with sequence or identity. – Aaron Bertrand Sep 29 '14 at 11:33
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.

Improve this answer
1
  • 1
    An interesting idea but the reliance on a sequence object to generate the numbers in the first place means gaps may still occur (e.g. if cache is lost after a restart). Also "sometimes our auto numbers are going to be in the wrong time order" means "sometimes there will be gaps until a later insert claims that unused number" – Martin Smith Nov 20 '16 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.