Identity column increment jump

Question

I am experiencing something funny in my database. The primary key increases like:

I'm using EntityFramework or sometimes LINQ to SQL.

http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity — ta.speot.is, Aug 07 '13 at 12:56
It happens when SQL server 2012 looses its pre-allocated sequence numbers... — OzrenTkalcecKrznaric, Aug 07 '13 at 12:58

score 4 · Accepted Answer · edited Sep 10 '16 at 20:59

4

It happens when SQL server 2012 loses its pre-allocated sequence numbers.

If you want to get rid of that, one option is to use traceflag:

DBCC TRACEON (272)

Another option is to use a sequence (with no caching) instead of identity:

CREATE SEQUENCE MySeq AS int
  START WITH 1
  INCREMENT BY 1
  NO CACHE;

See this: http://www.big.info/2013/01/how-to-solve-sql-server-2012-identity.html

edited Sep 10 '16 at 20:59

answered Aug 07 '13 at 13:02

OzrenTkalcecKrznaric

5,535
4
34
57

Bless you all. thumbs up – ojorma Aug 07 '13 at 13:11

score 1 · Answer 2 · answered Aug 07 '13 at 14:34

This is all perfectly normal. Microsoft added sequences in SQL Server 2012, Have a look here a link for some explanation.

If you want to have the old behaviour, you can:

use trace flag 272 - this will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag. use a sequence generator with the NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx)

Identity column increment jump

2 Answers2