ojorma ojorma - 2 months ago 21
ASP.NET (C#) Question

Identity column increment jump

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

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

Comments