Questions about increment operator

Question

I have a table with a field that increments. The values for that field are 1, 2, 3...60 and then the field is 1060.

I don't know why?

The next value for id must be 61.

Did you delete any data from the table? If you did then you might need to reseed the identity. — Taryn, Aug 20 '13 at 14:05
possible duplicate of [Reset AutoIncrement in SqlServer after Delete](http://stackoverflow.com/questions/510121/reset-autoincrement-in-sqlserver-after-delete) — Taryn, Aug 20 '13 at 14:06
If you're relying on your `IDENTITY` field always having an unbroken "chain" of integer values then I'm afraid you're using it for the wrong purpose. `IDENTITY` fields are **surrogate keys** and therefore have no real-world meaning. — gvee, Aug 20 '13 at 14:13
possible duplicate of [SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry](http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry) — Nenad Zivkovic, Aug 20 '13 at 14:39

Nenad Zivkovic · Answer 1 · 2013-08-22T10:33:34.063

3

This is a new feature of SQL Server 2012. Identity columns use Sequences and values are cashed by default to speed up when inserting new rows. When unplanned shutdown of SQL Server occurs, cache is lost and Identity values continue with gap (of usually 1000 or 10000) created.

edited Aug 22 '13 at 10:33

answered Aug 20 '13 at 14:49

Nenad Zivkovic

18,221
6
42
55

score 1 · Answer 2 · answered Aug 20 '13 at 14:27

There could be several reasons - some of the most likely:

999 records were deleted
999 inserts were attempted and failed
Something reset the identity value using DBCC CHECKIDENT ({table}, RESEED, {value});
Something inserted a record with a specific ID using SET IDENTITY_INSERT ON

Autoincrement fields are not guaranteed to be consecutive. Thy are guaranteed to be unique. If you need them to be consecutive, then I would recommend keeping track of the next ID yourself, which will require you to think about concurrency, foreign key references, etc.

score 0 · Answer 3 · answered Aug 20 '13 at 14:21

Am assuming you mean an IDENTITY field with an increment of 1. This increases the value by 1 each time a new record is inserted.

To quote:

If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.

As alluded to in the comments it's likely this table once had data populated with all the missing values but these were deleted. (As this is quite a big chunk maybe this was done in bulk?)

Questions about increment operator

3 Answers3