奇数SQL Server 2012 IDENTITY问题 [英] Odd SQL Server 2012 IDENTITY issue

问题描述

我从来没有见过这个事情，很奇怪。

我有一个本来正在开发的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 （long），没有SP，触发器或任何其他程序化内容。 / p>

  [Id] [bigint] IDENTITY（1,1）NOT NULL，
  / pre> 
 
 
非常混乱，有其他人看到这种情况吗？
解决方案
 p> This 博客文章有一些额外的细节。看起来像2012年，身份被实现为一个序列。默认情况下，序列具有缓存。如果缓存丢失，您将丢失缓存中的序列值。
 
 
 
提出的解决方案是创建一个序列， no cache  
  CREATE SEQUENCE TEST_Sequence 
 AS INT 
 START with 1 
 INCREMENT BY 1 
没有CACHE 
  
据我看到，身份列背后的顺序是无形。您不能更改其禁用缓存的属性。
 
 
 
要将其用于Entity Framework，您可以设置主键的  StoredGeneratedPattern  到已计算。然后，您可以在中生成身份服务器端，而不是插入触发器：
  if exists（select * from sys.sequences where name ='Sequence1'）
 drop sequence Sequence1 
如果存在（select * from sys.tables where name ='Table1'）
 drop table Table1 
如果存在（select * from sys.triggers where name ='Trigger1'）
 drop trigger Trigger1 
 go 
创建序列Sequence1 
 as int 
以1 
开始增加1 
无缓存
 go 
创建表Table1 
（
 id int primary key，
 col1 varchar （50）
）
 go 
创建触发器Trigger1 
在Table1上
而不是插入
作为
 insert Table1 
（ID ，col1）
为Sequence1选择下一个值
，col1 
从插入
 go 
 insert Table1（col1）values（'row1'）; 
 insert Table1（col1）values（'row2'）; 
 insert Table1（col1）values（'row3'）; 
 
从表1中选择* 
 
  
如果您找到更好的解决方案，让我知道：）
 
I've never seen this happen before, very odd.

I have a local SQL Server 2012 Express database that I'm developing against. Running a simple suite of tests using the TestDrive plugin and accessing the database with EF v5.

I just ran a test that inserts a record into the database. I had 9 rows in the table going from id 1-9. The next insert and the ID jumped by exactly 10000 !!!!

The Id column goes:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10009
I know failed inserts also increment the ID but I can guarantee that 10,000 didn't fail to insert in the 5 seconds between test runs ...

The table structure is really simple, a bunch of columns and one auto incrementing, identity column of type bigint (long), no SPs, triggers or any other programmatic content.
[Id] [bigint] IDENTITY(1,1) NOT NULL,
Very confusing, has anyone else seen this happening?
解决方案
This blog post has some additional details.  It looks like in 2012, identity is implemented as a sequence.  And by default, a sequence has a cache.  If the cache is lost you lose the sequence values in the cache.

The proposed solution is to create a sequence with no cache:
CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    NO CACHE
As far as I can see, the sequence behind an identity column is invisible.  You can't change it's properties to disable caching.

To use this with Entity Framework, you could set the primary key's StoredGeneratedPattern to Computed.  Then you could generate the identity server-side in an instead of insert trigger:
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
If you find a better solution, let me know :)

                        
这篇关于奇数SQL Server 2012 IDENTITY问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！