Identity Column jumps by Seed value - by KB996

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 743300 Comments
Status Closed Workarounds
Type Bug Repros 13
Opened 5/23/2012 9:20:32 AM
Access Restriction Public


All of my tables have an identity column as primary key, with seed = 1000, increment = 1. Table row primary key starts out at 1000, 1001, 1002. then I restart the server and it jumps by the amount in the seed value. so I have 1000, 1001, 1002, restart, 2002, 2003, restart, 3003, 3004, restart, 4004, 4005 and so on.  Is this a know bug?
Sign in to post a comment.
Posted by Halion on 12/11/2013 at 4:01 PM
Using SQL 2012, SP1 CU5. What a mess. If it's too hard for Microsoft to fix whatever bug inside the engine is causing this, can someone there in Microsoft look into some possible workarounds?

e.g. Would it help if we manually do a checkpoint before shutting down the server?

Having identity values jump these huge values on a simple server restart is unacceptable.
Posted by paul christo on 11/19/2013 at 11:11 PM
hi, we are using SQL Server 2012 database, here we are facing auto incrementing in all primary key identity column tables. how to resolve this problem. please guide us.
Posted by stmarti1 on 3/5/2013 at 11:54 AM
This is a serious bug and shouldn't be "by design".

Whoever closed this, please at least read the link provided by the first poster, and reopen the issue or mark as duplicate. Thanks.
Posted by Microsoft on 1/25/2013 at 3:38 PM
Thanks for your feedback. This behavior is actually by design as we try to guarantee uniqueness of the ID rather than making sure we don't have gaps. As a result, we do skip some values just in case in certain scenarios so that we don't have any issues around accidentally repeating numbers.
Posted by findjammer on 12/1/2012 at 12:04 AM
I've just seen this happen on a test SQL 2012 database. My IDs went 1,2,3,4,5,6,7,8,9, restarted SQL and the next Id was 10009 - 10021 etc ... restart, next id is 2021.

My ID column is defined like this:

[Id] [bigint] IDENTITY(1,1) NOT NULL,
Posted by Peter3412 on 6/20/2012 at 9:05 AM
I have the same problem. Test database with nothing else going on. Insert a row, restart SQL 2012 service via windows service manager restart option and my IDs jumped from 5 to 10002 (Big int identity field). This does not occur every time maybe 1 out of two restartarts.
Posted by Dominic Goulet on 6/15/2012 at 6:32 AM
Also, if you do a SET IDENTITY_INSERT [TableNameHere] and then insert a negative key, it also jumps by the seed value. I work in the BI field and have to insert negative values in tables to support Kimball method, like -1 = bad value, -2 = missing value, -3 = not happened yet, etc.

I have come to use a IDENTITY(0,1) instead of 1,1 to actually start at one when running my initial scripts that inserts the negative keys.
Posted by barbaros on 6/5/2012 at 11:52 PM
Same here. All my Ids messed up. Any workarounds?
Posted by Anna Ellis on 5/24/2012 at 1:22 AM
Also see Grant Fritchey's post here: