Odd SQL Server 2012 IDENTITY issue

Question

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?

Answer 1

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 :)

Answer 2

If you will call "Checkpoint" command after each insert query, it will solve your problem.

For more information, please read out Checkpoint in SQL Server