SQL Server identity number is not consecutive

Question

I am creating a database table with an index number (CustRef) for each row.

The index starts from 1 and increases 1 with each new row. The query language as followed:

CREATE TABLE [dbo].[CustDetails]
(
    [CustRef] [int] IDENTITY(1,1) NOT NULL,
    [LName] [nchar](25) NOT NULL,
    [FName] [nchar](25) NOT NULL,
    [Address] [nchar](80) NULL,
    [Suburb] [nchar](25) NULL,
    [State] [nchar](5) NULL,
    [PCode] [nchar](5) NULL,

    CONSTRAINT [PK_CustDetails] 
        PRIMARY KEY CLUSTERED ([CustRef] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

The table is created successfully and I was testing it by inserting some sample data. At one stage, I deleted a row, which the index number (CustRef) was 6. After I deleted index 6 row and continue inserting sample data.

Unfortunately, the index number is not consecutive. In other word, I was expecting the new data content will use the index 6 as its row index number. However, new entries skip index 6, it starts from index 7.

As you can see from the above screenshot, between index 5 and 7, index 6 is missing.

How to resolve this issue? Thanks in advance.

Thats how identity columns in SQL Server work - how is it a problem? You shouldn't care what your ID is. — Dale K, May 20 '21 at 04:29
It is a **good thing** that deleted identity values don't get "recycled" ! Don't waste any more effort on trying to do this .... an identity needs to be **unique**, above all. — marc_s, May 20 '21 at 04:37
Also as per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, May 20 '21 at 04:40
There is no issue. This is how identity columns are documented as working. They are guaranteed to be increasing, but not gapless. This is a feature that improves performance. — Gordon Linoff, May 20 '21 at 12:24

pwilcox · Answer 1 · 2021-05-20T18:23:02.010

Well, if it didn't work that way, then you would run into this:

insert custRef ...; // id = 1
insert custRef ...; // id = 2

insert custRef (fName, lName)
values ('Jane', 'Goodall'); 
// id = 3

Jane Goodall! She deserves a prize.

declare @giveAPrizeTo int = (
    select   id 
    from     custRef 
    where    fname = 'Jane'
    and      lname = 'Goodall'
);

We'll actually deliver it in a bit.

But first, another task. Customers that can't be contacted aren't really useful. The boss asked that we keep the table pure.

delete custRef where address is null;
alter table custRef alter column address nchar(80) not null;

Okay, well, moving on, add the next person.

insert custRef (fName, lName, address)
values ('Jeffrey', 'Dahmer', '1234 W Somewhere; Milwaukee, Wisconsin 12345');

In this hypothetical dialect where id's get recycled, Jeffrey Dahmer now has id = 3.

Hmm, that's interesting. I should be careful of the newest customer. Well, I got distracted, what was I doing? Oh yeah, that prize! I'd better deliver it.

declare @message nvarchar(max) = (

    select    'Congratulations ' + fName + ' ' + lName ', ' + 
              'we support your good works.  Have a prize!' 
    from      custRef
    where     id = @giveAPrizeTo;

);

print (@message);

Congratulations Jeffrey Dahmer, we support your good works. Have a Prize!

Oops!

score 1 · Answer 2 · answered May 20 '21 at 06:31

In your table CustDetails column CustRef is identity column. It auto increments at 1,2,3,4,5,6 now you deleted last row i.e. delete from CustDetails where CustRef=6. Technically next row will be inserted is 7 because SQL has already assigned value 6 to previous row. In other scenario if you had entered upto 1,2,3,4,5,6,7,8,9 and now deleting 6th row. what do you expect next value according to your requirement? 10 or 6?

I had implemented logic when client was asking to provide facility to delete last row only and no number to be skipped, I had write a logic of maxvalue + 1 for next row. you can write as remove identity and max(CustRef)+1 at time of insert. Alternatively you can use dbcc reseed that after each delete you write

declare @id int = 0
SELECT @id =  MAX(CustRef) FROM CustDetails
DBCC CHECKIDENT ('dbo.CustDetails', reseed, @id)

this will tell sql to reset value to last max. which is not at all recommended way to do so. this is just an option but yeah you can choose remove identity and write logic for get max(CustRef) before insert statement and increment it and insert into table CustDetails.

score 1 · Accepted Answer · answered May 20 '21 at 11:38

1. Create the table

 CREATE TABLE [dbo].[CustDetails]
 (
 [CustRef] [int] IDENTITY(1,1) NOT NULL,
 [LName] [nchar](25) NOT NULL,
 [FName] [nchar](25) NOT NULL,
 [Address] [nchar](80) NULL,
 [Suburb] [nchar](25) NULL,
 [State] [nchar](5) NULL,
 [PCode] [nchar](5) NULL,


 ) 
GO

Insert records
deleted the last record. in my case it was 4th record.
Set identity insert on

SET IDENTITY_INSERT [dbo].[CustDetails] ON;
Inserted the record with identity column included.

insert into [dbo].[CustDetails] ([CustRef] , [LName], [FName] , [Address],
[Suburb], [State] , [PCode])values (4, 'test4', 'test','test','test','test','test')

this approach is good for testing. But normally, unless you truncate the table, your index will not reset in identity columns in SQL server as it is already assigned. So this is kind of a solution. using Identity insert you get the row count and by based on max(row count) you can set identity column value when inserting a record.

SQL Server identity number is not consecutive

3 Answers3