为什么英孚的身份证号会跃升为一个大数字?(复制)

This question already has an answer here:

这个问题已经有了答案:

Identity increment is jumping in SQL Server database 6 answers
身份增值是跳SQL Server数据库6的答案

i use entity framework 6.0 on my website .

我在我的网站上使用了entity framework 6.0。

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;

我有一个表来插入一些数据这张表有ID (int)和我的identity seed = 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.

有时，当我在sql management studio中查看这个表时，我遇到的方法是将这个ID跳到大数字，例如从30跳到10024。

why this happens? is it a bug or what?

为什么发生这种情况呢?是bug还是什么?

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?

这是代码正常吗?那么我的桌子上发生了什么，那个身份号码跳到了一个大数字上?

2 个解决方案

#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).

可能有很多原因——一个可能是插入994条记录的失败尝试(或者多次失败尝试，总共994条记录)。

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.

标识列不保证连续的id;它保证独特的id。如果插入尝试失败(或者是回滚的事务的一部分)，则将生成的ID不会被重用。

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

另一个选项是在使用SET IDENTITY_INSERT之后插入特定ID。

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.

在任何情况下，你都有超过20亿的id，所以有10,000左右的缺口不应该引起恐慌。如果随着时间的推移，差距越来越大，那么你可能会面临更大的问题，因为差距可能呈指数级增长。

#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.

在服务器重新启动或其他故障之后的连续值——sql server可能由于性能原因缓存标识值，并且在数据库故障或服务器重新启动期间可能丢失一些分配的值。这可能导致插入时标识值出现空白。如果不能接受间隔，那么应用程序应该使用带有NOCACHE选项的序列生成器，或者使用它们自己的机制来生成键值。

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.

值的重用——对于具有特定种子/增量的给定标识属性，引擎不会重用标识值。如果特定的insert语句失败，或者insert语句被回滚，那么消耗的标识值就会丢失，并且不会再次生成。这可能会在生成后续标识值时导致空白。

#1