7

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jammer
  • 9,969
  • 11
  • 68
  • 115
  • I don't see how it can be my code. The code doesn't do anything with the ID and it's an INSERT command, not an UPDATE command and if the INSERT tried to apply an ID it would simply ignore the ID value ... – Jammer Dec 01 '12 at 07:55
  • Seems this might be a bug in 2012 as it's reported here by others as well ... http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value#tabs I've just added a log for this as well ... – Jammer Dec 01 '12 at 08:05
  • Seems it's caused by a restart of the SQL engine ... but why it would jump 10000 when my seed is 1 is a bit crazy ... – Jammer Dec 01 '12 at 08:06
  • 1
    https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity – Mitch Wheat Dec 01 '12 at 08:13
  • 1
    This is a known issue. Unfortunately, MS hasn't paid attention. This issue is causing major headaches for us. – Allen King Jul 21 '14 at 19:11
  • Yeah, it's far from ideal imho. – Jammer Jul 21 '14 at 19:32

2 Answers2

2

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

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Ahhh ... that is interesting. I'll see if I can get this no cache approach working with EF ... – Jammer Dec 01 '12 at 08:50
  • Hmm ... that all looks a bit messy to me :( I'll see if I can find anything about it and report back – Jammer Dec 01 '12 at 09:08
  • Actually, you know what. I'm not even sure I care, it's a bigint so I'm not going to run out anytime soon. My Ids aren't used in any way that is dependant on how they are created either and if it only happens when SQLServer is restarted in production that wont be often (famous last words!) ... at least I'm not concerned now, may well go back to 2008 R2 for real production deployment ... – Jammer Dec 01 '12 at 09:34
  • Agreed. I'd tweak this far only if identity absolutely had to be consecutive. – Andomar Dec 01 '12 at 09:59
0

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

Palak Shah
  • 1