T-SQL simple table with Primary key

Question

I have created a simple table, with ID column as Primary key and NOT NULL.
So I am expecting whenever records are being inserted the ID table sequentially fills the table starting from 1,2,3....n

When ever I am inserting the records are getting inserted fine, but sometime the ID is missing(skipping) the count. It is inserting like 1,2,4,5,7,....
How can I have this ID (PK) should increase only sequentially ?

Answer 1

Hi @Rock Hitman ,

It seems that you are using IDENTITY to increment the ID column.
IDENTITY doesn't guarantee a true sequential running numbers without gaps.
You can try to use a SEQUENCE for that.

Useful link: sql-server-sequence

Answer 2

If you want your IDs to be contiguous, you need to roll your own. Both IDENTITY and sequences are designed to produce gaps. This is because they are not tools to auto-generate IDs in general. These are tools to permit you to auto-generate Ids in a high-concurrency environment.

To roll your own, do:

    BEGIN TRANSACTION
    SELECT @id = isnull(MAX(id), 0) + 1 FROM tbl 
    INSERT tbl(id,`enter code here` col2, col2, ...)
        VALUES(@id, @val2, @val3...)
   COMMIT TRANSACTION

Then again, in many cases we use auto-generated ids just we because we want some numbers, and gaps are not really a problem. There applications where business rules mandates unbroken series of numbers, not the least in the financial world. But they are not that common.

Answer 3

actually below is my code : whenever there is an insert, I want to make sure the ID is increased sequentially

ALTER PROCEDURE [dbo].[usp_TeraData_Insert]
    (
        @tv_TeraData as [TeraDataType] READONLY  
    )
AS
BEGIN

    SET NOCOUNT, XACT_ABORT ON;
        INSERT INTO dbo.TeraData  
        (
                [VhlMakeID]
            ,   [VhlMake]
            ,   [VhlModelID]
            ,   [VhlModel]
            ,   [VhlSubModelID]
            ,   [VhlSubModel]
            ,   [VhlTypeID]
            ,   [VhlType]
            ,   [VhlYear]
            ,   [CreatedDate]
        )
        SELECT *, CURRENT_TIMESTAMP
 from (select distinct [VhlMakeID],[VhlMake],[VhlModelID],[VhlModel],
[VhlubModelID],[VhlSubModel],[VhlTypeID],[VhlType],[VhlYear] 
        FROM  @mlt_TeraData) AS Tera
        WHERE NOT EXISTS (SELECT 1 
FROM dbo.TeraData 
                            Where TeraData.VhlMakeID = Tera.VhlMakeID
and TeraData.VhlSubModel = Tera.VhlSubModel                           
                              and TeraData.VhlTypeID = Tera.VhlTypeID                             
                              and TeraData.VhlType = Tera.VhlType
                              and TeraData.VhlYear = Tera.VhlYear
                              and TeraData.VhlMake = Tera.VhlMake                              
                              and TeraData.VhlModelID = Tera.VhlModelID
                              and TeraData.VhlModel = Tera.VhlModel
                              and TeraData.VhlSubModelID = Tera.VhlSubModelID

                              );

END

Answer 4

Hi @Rock Hitman ,

From SQL Server 2012 version, when SQL Server instance is restarted then its auto Identity column value is jumped based on identity column datatype.

We can do two things to stop that jump.
Using Sequence or Register -t272 to SQL Server Startup Parameter

Using Sequence
First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:

CREATE SEQUENCE Id_Sequence  
    AS INT  
    START WITH 1  
    INCREMENT BY 1  
    MINVALUE 0  
    NO MAXVALUE  
   NO CACHE  
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');  
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');  

When the digital value jumps very small, it is not caused by restart, but it can also be solved by sequence.

create table #test(id int,name char(15))  
  
--create Squence   
CREATE SEQUENCE TestSquence   
AS bigint    
START WITH 1   --Starting value  
INCREMENT BY 1  --Increment  
--MINVALUE 100   --Max  
--MAXVALUE 200  --Minimum  
CYCLE  -- Set whether to cycle, the default cycle option is NO CYCLE  
CACHE 3 --Cache  
  
--Add to table  
insert into #test (id,name)values (NEXT VALUE FOR  TestSquence,'aa')  
insert into #test (id,name)values (NEXT VALUE FOR  TestSquence,'bb')  
  
select * from #test  
  
--Query current Squence information  
SELECT * FROM sys.sequences WHERE name = 'TestSquence' ;  
SELECT current_value FROM sys.sequences WHERE name = 'TestSquence' ;  
  
--Delete sequence  
drop SEQUENCE TestSquence  
  
drop table #test  

For more details, please refer to：
SQL Server 2012 Auto Identity Column Value Jump Issue
CREATE SEQUENCE (Transact-SQL)

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.