SQL Server 2012的奇怪IDENTITY问题

Question

我从未见过这种情况，非常奇怪。 我正在开发一个针对本地 SQL Server 2012 Express 数据库的应用程序。使用 TestDrive 插件运行一系列简单的测试，并使用 EF v5 访问该数据库。 我刚刚运行了一个向数据库插入记录的测试。在表中有 9 行，id 为 1-9。下一次插入记录时，ID 突然增加了 10000！ Id 列的值如下：

1, 2, 3, 4, 5, 6, 7, 8, 9, 10009

我知道插入失败也会增加ID，但我可以保证在测试运行之间的5秒钟内没有10,000个插入失败...

表结构非常简单，一堆列和一个自动递增的标识列，类型为bigint（长整型），没有存储过程、触发器或任何其他编程内容。

[Id] [bigint] IDENTITY(1,1) NOT NULL,

非常混乱，有其他人看到这种情况发生过吗？

- Jammer

---

我不明白这怎么可能是我的代码。代码没有对ID进行任何操作，而且它是一个INSERT命令，而不是UPDATE命令，如果INSERT尝试应用ID，它将简单地忽略ID值... - Jammer

似乎这可能是2012年的一个错误，因为其他人也在这里报告了... http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value#tabs 我也为此添加了一个日志... - Jammer

似乎是由于 SQL 引擎的重启引起的……但为什么我的种子是1，它会跳10000，有点疯狂... - Jammer

https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity - Mitch Wheat

这是一个已知的问题。不幸的是，微软没有关注它。这个问题给我们带来了很大的困扰。 - Allen King

是的，在我看来，这远非理想。 - Jammer

Answer 1

这篇博客文章有一些额外的细节。看起来在2012年，identity被实现为一个序列。默认情况下，序列具有缓存。如果缓存丢失，则会丢失缓存中的序列值。

建议的解决方案是创建一个没有缓存的序列：no cache。

CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    NO CACHE

据我所见，标识列背后的序列是不可见的。您无法更改其属性以禁用缓存。

要在Entity Framework中使用它，您可以将主键的StoredGeneratedPattern设置为Computed。然后，您可以在一个instead of insert触发器中在服务器端生成身份标识：

if exists (select * from sys.sequences where name = 'Sequence1')
    drop sequence Sequence1
if exists (select * from sys.tables where name = 'Table1')
    drop table Table1
if exists (select * from sys.triggers where name = 'Trigger1')
    drop trigger Trigger1
go
create sequence Sequence1
    as int
    start with 1
    increment by 1
    no cache
go
create table Table1
    (
    id int primary key,
    col1 varchar(50)
    )
go
create trigger Trigger1
    on Table1
    instead of insert
as
insert  Table1
        (ID, col1)
select  next value for Sequence1
,       col1
from    inserted
go
insert Table1 (col1) values ('row1');
insert Table1 (col1) values ('row2');
insert Table1 (col1) values ('row3');

select  * 
from    Table1

如果你找到了更好的解决方案，请告诉我 :)

- Andomar

---

啊哈...这很有趣。我会看看能否让这个无缓存的方法与EF一起工作... - Jammer

嗯...这看起来有点凌乱:( 我会看看能否找到相关信息并回报。 - Jammer

其实你知道吗，我甚至不确定我在意不在乎，因为这是一个大整数，所以我不会很快用完。我的 ID 也没有以任何依赖于它们的创建方式使用，如果仅在生产中重新启动 SQLServer 时才发生这种情况，那么这种情况不会经常发生（著名的最后一句话！）... 至少现在我不担心，也许真正的生产部署会回退到 2008 R2 ... - Jammer

同意。只有在身份必须连续时，我才会进行这样的微调。 - Andomar

Answer 2

如果你在每个插入查询之后调用“Checkpoint”命令，它将解决你的问题。

如需更多信息，请阅读 SQL Server 中的 Checkpoint。

- Palak Shah

---

数据库中大约有300个插入查询。这样行不通。微软需要解决这个问题。 - Allen King