SQL Identity column increases by 2 or 3

Question

I have a new clustered database and I've been moving data into the tables from our old database. When I import the data, everything works fine, but when I manually insert a record the Identity column does not follow the next identity number. So, for example, I import 100 records into the table and the Identity column shows 1-100, but if I then manually add a row to the database, I'll get 102 or 103, not 101.

Any ideas why this is happening?

It is happening across all of my tables and it's getting very frustrating as sometimes the gap between Identities is up to 4 or 5.

Answer 1

If you "test" your insert within a transaction and a rollback. And then you run it for real. The insert within the transaction will increment the identity field by one, even if you then rollback.

Answer 2

You shouldn't depend on identity columns following any particular pattern (e.g. always increasing by only 1). The only guarantee is that the values currently in the table will be unique.

Answer 3

IMO, one should not pay attention to gaps in identity columns. The whole point of such a surrogate key (as opposed to a natural key) is to not have to worry about any sort of "correctness".

Now, like Robin said, you may have statements that are rolling back or erroring out which causes gaps in the identity.

The other thing that can cause gaps is the definition of the identity itself. Run this statement and see what I mean

Declare @MyTable table ( MyColumn1 Int Identity (1, 5))

INSERT INTO @MyTable Default Values
INSERT INTO @MyTable Default Values
INSERT INTO @MyTable Default Values
INSERT INTO @MyTable Default Values
INSERT INTO @MyTable Default Values
INSERT INTO @MyTable Default Values

SELECT * FROM @MyTable

Answer 4

You cannot depend on Identity columns to always be sequential, because the increment maintained by SQL Server and is preserved across transactions. Although this increment is reset when altering the table structure or when toggling SET IDENTITY_INSERT to ON and back to OFF, I would not advise it.

If you really depend on sequential IDs, you should create a non-identity column and write your own logic to make sure it stays sequential.

Answer 5

If you are adding the row through SSMS and you attempt to insert the row and experience any type of constraint violation (i.e. Datatype Violation or Foreign Key Violation), the identity is incremented by 1 even though the row is not successfully inserted into the table. This may be what is happening?