English 中文(简体)
SQL Identity column increases by 2 or 3
原标题:

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.

最佳回答

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.

问题回答

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.

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

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.

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?





相关问题
Performance impact of indexed view in MS SQL Server 2008

Does anyone have experience with using indexed view in MS SQL Server 2008? I am trying to find out how does indexed view affect performance of insert / update statements, that are adding / updating ...

Lock Escalation - What s happening here?

While altering a table (removing a column) in SQL Server 2008, I clicked the Generate Change Script button and I noticed that the change script it generated drops the column, says "go" and then runs ...

Round to nearest 5 in SQL Server

I have a Money column in my SQL Server 2008 table. In my below query how can I round it to nearest 5$ select FineAmount from tickets Thanks

热门标签