why identity number in ef jump to a big number?

Question

i use entity framework 6.0 on my website .

i have a table to inserting some data and this table has identity column like ID (int) and my identity seed is equal to 1;

some time when i look at this table by sql management studio i encounter by jumping this ID to the big number ,for example from 30 jumped to 10024.

why this happens? is it a bug or what?

i past here my sample code that i insert a data to this table by some codes like this:

 using (var context = new MyModelDBEntities())
        {
             mytable mt=new mytable;
             mt.name=""; //for example
            context.mytables.add(mt);
            context.SaveChanges();
        }

is this codes normal? what happend to my table then, that identity number jump to a big number?

Are you sure that you did not deleted all rows between 30 and 10024? SQL Server will not reuse ID it will just keep adding to last value. — , Feb 04 '14 at 21:11
In that case just like it was already mentioned in answers it could come from failed inserts. Can you add SQL for table definition? Does your column include `IDENTITY (1,1)` on it? or are you relying on your c# code to increment it? — , Feb 04 '14 at 21:18

score 8 · Accepted Answer · answered Feb 04 '14 at 21:12

8

Could be many reasons - one could be a failed attempt to insert 9,994 records (or multiple failed attempts that total 9,994 records).

An identity column does not guarantee consecutive IDs; it guarantees unique IDs. If an insert attempt fails (or is part of a transaction that is rolled back) the ID that would have been generated is not re-used.

Another option is something inserting a specific ID after using SET IDENTITY_INSERT ON.

In any case you've got over 2 billion IDs available so a gap of 10,000 or so shouldn't be cause for alarm. If the gaps are getting bigger as time goes on then you may have a bigger problem as the gaps may be growing exponentially.

answered Feb 04 '14 at 21:12

D Stanley

149,601
11
178
240

can you explain your answer on paragraph four – motevalizadeh Feb 04 '14 at 21:18
@motevallizadeh I've seen bugs where the "gap" is equal to the # of records currently in the table, so the "gaps" grow exponentially and you reach the 2 Billion limit sooner that of the gaps were consistent. It's rare (and usually only happens with `IDENTITY_INSERT ON`) but something to watch out for. – D Stanley Feb 04 '14 at 21:20
@motevallizadeh Also that seems unlikely in this case as you jump from 30 to 10,000. I would bet it's failed inserts. – D Stanley Feb 04 '14 at 21:21
+1 for identity insert AND failed inserts. Very true, if any code, scheduled job, or SQL script uses `SET IDENTITY_INSERT myTable ON`, then they can insert any value they want and the the identity is re-seeded to the max value in that identity column after `SET IDENTITY_INSERT myTable OFF` is called. Here is a SQL Fiddle that shows the failed insert and identity insert causing gaps. http://sqlfiddle.com/#!3/2571f/1 – BateTech Feb 04 '14 at 23:39
@BateTech Thanks SQL Fiddle is a nice tools,I will use it. – motevalizadeh Feb 05 '14 at 12:12
This don't answer PO question. In my small project which do simple `CRUD` operations I'm pretty sure that I don't have 1000 failed inserts and I never used `SET IDENTITY_INSERT myTable OFF`. It is more likely some internal issues with Microsoft productions. It is not possible to explain this anymore => `1,2,3,4,5,6,7,1002,1003,1004` – Masoud Keshavarz Feb 05 '19 at 07:41

score 3 · Answer 2 · answered Feb 04 '14 at 21:12

Identity property on a column does not guarantee that it won't have gaps. Here is from the documentation:

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

why identity number in ef jump to a big number?

2 Answers2