Identities Appearing in Tables have Gaps

Question

We have begun to see identities created in some of our tables are no longer precisely sequential. That is to say they remain incrementally higher but there are large gaps in the values.

For instance, the sequence is {1,2,3.. .. 97,98,99} and then a jump to {1092,1093,1094.. .. 1097,1098,1099} followed by another gap and then {4231,4232,4233.. .. 4257,4258,4259}.

Can anyone shed any light on this behaviour?

Could it be explained by a `delete` of the missing records? A lot of the identity fields I have created in the past only ever auto-increment. They don't "fill in the gaps". — Jess, Mar 21 '13 at 16:55

Charles Bretana · Answer 1 · 2013-03-21T17:05:27.743

2

If you execute an insert to a table that has an identity column, and the insert fails (for ANY reason), the identity value is still incremented and the next insert will leave a gap. Also, if you delete rows there will obviously be gaps.

NEVER rely on, or use, the actual value of a surrogate key, or identity for anything other than as a "connection value" to connect row or rows in one table to rows in another table. Certainly never rely on the sequence of values being contiguous, nor even that they are chronological increasing.

edited Mar 21 '13 at 17:05

answered Mar 21 '13 at 16:56

Charles Bretana

143,358
22
150
216

Thank, I understand. In our case the gaps include so many values that I believe is from serer reset as described below. Thanks, again, Greg. – Greg Hayden Mar 21 '13 at 17:07
if the server reset happens during an insert operation, then yes, the insert would fail, and any identity values allocated, but not committed would end up being rolled back, thus showing as a gap. – Charles Bretana Mar 21 '13 at 17:27
1

@Charles The problem experienced is not a roll back of a single insert, it is a gap of hundreds or even thousands of values. It is a known bug as I show in my answer. – Aaron Bertrand Mar 21 '13 at 17:35
1

@Aaron, thanks, I reviewed link you provided. I did not know about that. As I understand it, the consequence of the issue referred to in link (unless you are misusing values of identity column) is that you will run out of potential identity values earlier than necessary based solely on identity datatype. Does that jive with your understanding? I would point out that the op's issue could have been caused by any of the other mentioned issues as well as by this bug. Any statement (or statements in a single Tx) that attempt to insert a large block of rows and fails would cause the same symptom. – Charles Bretana Mar 21 '13 at 19:39
@Charles right, but the fact that he's losing x000 at a time certainly makes it seem like it's not a batch of x000 rolled back inserts. – Aaron Bertrand Mar 21 '13 at 19:49
@Aaron, if normal usage patterns in his application do not involve single statement, or transactional, inserts of 100s of rows at a time, then yes, I would agree. My point was simply that, absent knowledge about that, it would be premature to jump to that conclusion. Again, thanks for educating me about this issue. – Charles Bretana Mar 22 '13 at 13:45

score 1 · Answer 2 · answered Mar 21 '13 at 17:02

Anytime you insert into a table with an identity column it increments the identity. If you delete the rows, or even if you rollback an insert into that table the identity column stays at the new increment.

Here's a script to show the effect of rolling back a transaction:

create table #temp (TheKey int identity(1,1), TheValue int)

insert into #Temp (TheValue) values (1)
select max(TheKey) from #Temp  --1 as expected

begin tran 
insert into #Temp (TheValue) values (1)
select max(TheKey) from #Temp  --2 as expected
rollback
select max(TheKey) from #Temp  --1 as expected

insert into #Temp (TheValue) values (1)
select max(TheKey) from #Temp  --3 a little bit of a surprise?

Thank you, I understand. In our case the gaps include so many values that I believe is from serer reset as described below. Thanks, again, Greg — Greg Hayden, Mar 21 '13 at 17:08

score 1 · Answer 3 · answered Mar 21 '13 at 17:05

1

It appears that the server caches values for performance and so if the server is reset after a power outage for instance then those values can be lost. See this article.

Consecutive values after server restart or other failures

answered Mar 21 '13 at 17:05

Greg Hayden

135
1
3
12

score 1 · Accepted Answer · answered Mar 21 '13 at 17:33

This is a known bug. Your large gaps are caused by things like failover, service restart, reboots, etc.

http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Until it is fixed, there's not much you can do about it except maybe have a startup procedure that reseeds the identity column on all affected tables.

Identities Appearing in Tables have Gaps

4 Answers4