Unanswered: are gaps possible in Identity values ?
coming from a Sybase background, I'm used to identity columns having significant gaps. E.g. first few rows have 1,2,3,..... But then, the following rows could be 500001,500002,500003, etc.
Now I'm writing a few stored procs for Sql 2000, and I need to find the __second__ record inserted. Can I rely on the Identity column simply being equal to min() + 1 ? The Sql 2000 documentation seems to suggest that, but I'm being careful, given past experiences.
I know a work round, but would take two queries. I have a primary key on the identity column. At the beginnning of my batch job, all records are deleted from the table, then the table is populated again.
as long as sql will populate the identity col it will be sequentally, until the (far) end is hit. But, for example, inserting rows with "identity off" might break this rule, so you should not rely on min()+1 for the second row.
The only way (that I've encountered) to get a gap between two sequential rows in your table is to insert new record(s), roll back the transaction (for some reason) and the insert a new one. In this case you will have a gap equal to the number of records that were inserted and rolled back.
But if you say that all records in your table are deleted and inserted again in the top of your store proc in one single batch (that I supposed it's like that) than you don't have to worry, you will have a continuous flow of values for you PK.