Identity seed increments out of sequence!

[programmher]

We have a Cold Fusion product that updates to a SQL 7 database. For some reason, there are "gaps" in the sequential numbers for the entered records after the records have updated to the SQL database. Has anyone had this happen to them?

Many thanks in advance! [sig][/sig]

 

[foxdev]

There could be several reasons:

1) records "in the middle" were deleted. Identity numbers, once assigned, are never adjusted automatically, and you wouldn't want this anyway.

2) someone has changed the Identity column's Seed value.

3) someone has set: [tt]SET IDENTITY_INSERT table_name ON[/tt] and someone/program is explicitly specifying a value for the Identity columns. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[kybernetikos]

Yes, this happened to me to and none of the above three options were true. In my product INSERT statements are fired at a table and the transaction commited every 10,000 records. I get small jumps (of 2-5) in the identity key. Very annoying! If anyone knows how to fix the I would be very grateful to know how??

 

[bperry]

In addition to foxdev's 3 points, I think I've heard there are also a few anomolies or bugs in Identity assignment. People do occasionally see gaps like this. Not much to be done about it.

 

[tlbroadbent]

There are many reasons for gaps in the sequence of Identity columns. Another reason, not already mentioned, is that SQL Server increments the identity column on an Insert but if the Insert fails, the identity value is not reset.

The only time these gaps become an issue is when the identity column is used as something more than a unique row identifier. Identity columns should not be used for data that will be displayed to the user. If you use an identity column as data that is meaningful to users, you and they must be willing to tolerate gaps in the sequence. A better alternaive is to control the generation of this type of data yourself. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[kybernetikos]

I have found that when I commit after each INSERT statement (not using transactions) the problem dissappears. It will make any process slightly slower but seems to be the only sure way of getting a sequential identity key.

 

[tlbroadbent]

kybernetikos,

Please explain what you mean. How do you Commit without using a transaction? What happens if the statement inserts multiple rows? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Dilyias]

Perhaps he meant he is wrapping each INSERT in its own transaction,

BEGIN TRAN
INSERT ..
COMMIT TRAN

or maybe he is just INSERTing...

or something..

 

[kybernetikos]

thbroadbent,

dilyais is correct. I'm only firing insert statments at the table. Have not come accross this problem using other methods (ADO etc), only when I wrap large numbers of insert statements in transactions.