Identity column increment jump [duplicate]

Question

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

1
2
3
4
5
6
7
8
1001

I'm using EntityFramework or sometimes LINQ to SQL.

Answer 1

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

Answer 2

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)