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)
user2438237
  • 141
  • 1
  • 4
  • 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. –  Sep 28 '14 at 15:24
  • You could use a key table, similar to the one in the answers to this question: http://dba.stackexchange.com/questions/36603/handling-concurrent-access-to-a-key-table-without-deadlocks-in-sql-server – Hannah Vernon Sep 28 '14 at 15:26
  • 1
    @PieterGeerkens : I am Preparing for 461 certification and it just a thought which crossed my mind.. :) – user2438237 Sep 28 '14 at 16:26
  • @a_horse_with_no_name : I am Preparing for 461 certification and it just a thought which crossed my mind.. :) Is that not even possible? – user2438237 Sep 28 '14 at 16:27
  • Is this SQL Server 2008 or 2012 (you have tagged both)? The sequence object isn't supported prior to 2012. – dartonw Sep 28 '14 at 17:18
  • 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
  • @dartonw : i am using SSMS 2012 also my question is not about violation of PK constraint. But it is about how to prevent gaps? – user2438237 Sep 28 '14 at 17:33
  • SSMS 2012 could be used with prior versions of SQL Server. The important thing is, what version is the server instance you are connecting to using SSMS? – dartonw Sep 28 '14 at 17:37
  • @dartonw : please see the details below :

    Microsoft SQL Server Management Studio 11.0.3128.0 Microsoft Analysis Services Client Tools 11.0.3128.0 Microsoft Data Access Components (MDAC) 6.2.9200.16384 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.10.9200.16843 Microsoft .NET Framework 4.0.30319.18449 Operating System 6.2.9200

     – user2438237 Sep 28 '14 at 17:43
  • @user2438237 that's why it was a comment and not an answer. – dartonw Sep 28 '14 at 18:07
  • 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
  • @AaronBertrand : here it is:

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: )

     – user2438237 Sep 30 '14 at 11:39

1 Answers1

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.

Sergey Nikolayev
  • 111
  • 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