why identity number in ef jump to a big number? [duplicate]

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?

Answer 1

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.

Answer 2

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.