T-SQL simple table with Primary key

Rock Hitman 46 Reputation points
2020-09-23T19:56:55.667+00:00

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 ?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,519 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,566 Reputation points
    2020-09-23T20:16:05.9+00:00

    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

    0 comments No comments

  2. Erland Sommarskog 99,071 Reputation points MVP
    2020-09-23T21:59:18.437+00:00

    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.


  3. Rock Hitman 46 Reputation points
    2020-09-23T22:35:47.257+00:00

    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
    

  4. EchoLiu-MSFT 14,571 Reputation points
    2020-09-24T06:37:00.887+00:00

    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  
    

    27953-image.png

    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.