How to stop Primary key Identity column skipping missing values

Question

I have a table with three columns ID, Name, Designation where column ID is a primary key and its Identity specification is enabled and it seeds by 1.

Suppose I have 10 rows in the table. In a sequence values of ID column is in sequence

1,2,3......10.

and user deletes 10th row then inserts a new row I am inserting will be having 11 as the value of ID column. How can I make to to increment last row value? I mean i want "10" as the value of new row as row before it will be having column ID value as 9.

I don't want to use MAX function of SQL Server, I want it to be auto incremented. Is there any way to achieve this?

P.S: i am using Entity framework to insert values in DB.

You **CANNOT** do this - nor **should you!** If `ID=10` has been handed out previously, it is a **horribly bad** idea to "recycle" that value! SQL Server has no provisions to do this, and there really is no need for this. Just get used to it - `INT IDENTITY` will **never** be totally consecutive - there will be gaps (and those are **NO PROBLEM** from a database point of view) — marc_s, Oct 12 '13 at 08:36
What's your goal here? If ID#5 is deleted, do you want the next insert to be 5, or do you want 6-10 to be decremented, or is it ok to delete from the middle, just not the end? Regardless, you won't be able to do this with EF and identities, but it would help to know exactly what you're trying to accomplish. — Joe Enos, Oct 12 '13 at 08:44
@DeepSharma So what? Compare it with people. If someone passes away, do all social security numbers get reassigned to others in order not to have any gaps? An ID value typically has no business use, but merely serves as a way to easily and uniquely identify a specific row. It has no actual real-world entity equivalent. — SchmitzIT, Oct 12 '13 at 08:46
I am only concerned about last row value and new values to be inserted. if ID#5 is deleted. then next value inserted will be having ID#(Lastrowvalue + 1). I don't want any value to be decremented. — Deep Sharma, Oct 12 '13 at 08:47
Then what's the point? If you're ok with gaps in the middle, then why do you care if a new gap is created at the end of the list? It won't be the end of the list for very long. — Joe Enos, Oct 12 '13 at 08:48
@SchmitzIT i am storing some values which have ID concatenated. and i am generating their value in code behind along with using max function for ID column by EF. — Deep Sharma, Oct 12 '13 at 08:50
@JoeEnos lets say i have last value as 10 and somebody deleted it. next value inserting will have 11 as its value. but i want it to be 10 as i am using this value in generating some code for user. — Deep Sharma, Oct 12 '13 at 08:52
@DeepSharma You are using max for ID column to generate values in client code? You realize what happens if two clients do that at the same time? — GSerg, Oct 12 '13 at 08:55
it may conflict i know. Do you have any workaround for this? @GSerg — Deep Sharma, Oct 12 '13 at 08:57
@DeepSharma Yes I do - insert the record, disregard gaps, ask the EF what is the ID of the newly inserted record, use that returned ID in your calculations. — GSerg, Oct 12 '13 at 09:00
You're not going to get a good answer for your question here on Stack Overflow because *there is no good answer to your question except this*: Don't do it. Leave the gaps. — Lasse V. Karlsen, Oct 12 '13 at 09:02
See http://stackoverflow.com/questions/19141855/afterupdate-leaves-gaps-in-primary-key, http://stackoverflow.com/questions/15368478/automatically-remove-gaps-in-primary-key-sequence, http://stackoverflow.com/questions/9743121/how-to-reuse-auto-increment-values. Voting to close as duplicate. You won't find better answers by reasking this question. — Lasse V. Karlsen, Oct 12 '13 at 09:05
Imagine you're logging errors or info messages - now you have an entry `Record 10 deleted` - is that the **first** incarnation of record no. 10? The second one? The third one? No one can tell. An auto-increment `ID` should be **UNIQUE** at all times - do **NOT** recycle and reuse id's - it'll come back and bite you sooner or later! Just don't do it - there's really no benefit, and a lot of problems.... — marc_s, Oct 12 '13 at 09:23
@DeepSharma - Regarding the concat IDs. What happens if at some point you forget to remove the ID from one table. You'd be stuck with duplicates or invalid references. Several people in this and related threads already told you it's bad practice and a bad idea. If you do not want to take their advice, then we really cannot help you further. — SchmitzIT, Oct 12 '13 at 10:15

Michael · Accepted Answer · 2020-09-22T16:56:27.767

For the person that likes live dangerously it is possible, though ill advised to reseed the identity column.

The TSQL is

dbcc checkident ([tableName], reseed, [previousHighestID])

Given the following table:

create table x ( a int identity(1,1), y varchar(max) )

Entity framework:

using (var context = new XContext())
{
    context.Database.SqlCommand("insert into x select 'abc'");//id 1
    context.Database.SqlCommand("insert into x select 'cba'");//id 2
    context.Database.SqlCommand("delete from x where a = 2");//delete id 2
    context.Database.SqlCommand("declare @identMax int; select @identMax = max(a) from x; dbcc checkident (x, reseed, @identMax)");
    //reseed identity to 1 ( so next insert will be id 2 )
    context.Database.SqlCommand("insert into x select 'def'");//id 2 (again!)
    var X = context.X.SqlQuery("select * from x").ToList();    
}

Really don't worry about a few missing values. An int, or bigint column can represent a lot of values.

I'm reminded of a Jack Handy deep thought:

"If you drop your keys into molten lava just let 'em go 'cause, man, they're gone."

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

you can do it manually .

Step 1 : Remove IsIdentity="ON" in your primary key (or Set IsIdentity in OFF)

step 2: Get the Last Row in your table by using EF query , and get the primary key column value at whenever you save data to database . (Load most recent related entity record on a list of entities in a single query )

step 3 : And Now if the last row primary key values is 9 ,then you can add +1 with that value and store the new values with primarykey column values is 10 ,

edited May 23 '17 at 11:57

Community

1
1

answered Oct 12 '13 at 09:46

Ramesh Rajendran

37,412
45
153
234

2

If you do that, you need to use a transaction and you need to lock the table with the first select so that other selects don't receive the same value. – Szymon Oct 12 '13 at 10:04

score 0 · Answer 3 · answered Oct 12 '13 at 14:22

0

The only time you want to recycle identity/auto incrementing field is when the value of that field is nearly exhausted or the values are heavily fragmented.

answered Oct 12 '13 at 14:22

Sameer

3,124
5
30
57

score -1 · Answer 4 · answered Oct 12 '13 at 08:39

-1

if you have an identity column you cannot do that, unlees the insert you want to do is a one time thing.. then you can try :

SET IDENTITY_INSERT XXX ON
GO

-- INSERT WITH THE IDENTITY VALUE YOU WANT

SET IDENTITY_INSERT XXX OFF
GO

answered Oct 12 '13 at 08:39

Jonysuise

1,790
13
11

2

i am inserting values in DB using Entity framework asp.net. – Deep Sharma Oct 12 '13 at 08:40
1

then i think you should reconsider using an identity column, whose purpose is to autoincrement a value so you dont have to worry about it. use max instead before each insert. – Jonysuise Oct 12 '13 at 08:43
i am storing some values which have ID concatenated. and i am generating their value in code behind using max function for ID column by EF. – Deep Sharma Oct 12 '13 at 08:49

How to stop Primary key Identity column skipping missing values

4 Answers4