Does SQL Server guarantee sequential inserting of an identity column?

Question

In other words, is the following "cursoring" approach guaranteed to work:

1. retrieve rows from DB
2. save the largest ID from the returned records for later, e.g. in LastMax
3. later, "SELECT * FROM MyTable WHERE Id > {0}", LastMax

In order for that to work, I have to be sure that every row I didn't get in step 1 has an Id greater than LastMax. Is this guaranteed, or can I run into weird race conditions?

Answer 1

Guaranteed as in absolutely under no circumstances whatsoever could you possibly get a value that might be less than or equal to the current maximum value? No, there is no such guarantee. That said, the circumstances under which that scenario could happen are limited:

1. Someone disables identity insert and inserts a value.
2. Someone reseeds the identity column.
3. Someone changes the sign of the increment value (i.e. instead of +1 it is changed to -1)

Assuming none of these circumstances, you are safe from race conditions creating a situation where the next value is lower than an existing value. That said, there is no guarantee that the rows will be committed in the order that of their identity values. For example:

1. Open a transaction, insert into your table with an identity column. Let's say it gets the value 42.
2. Insert and commit into the same table another value. Let's say it gets value 43.

Until the first transaction is committed, 43 exists but 42 does not. The identity column is simply reserving a value, it is not dictating the order of commits.

Answer 2

I think this can go wrong depending on the duration of transactions Consider the following sequence of events:

1. Transaction A starts
2. Transaction A performs insert - This creates a new entry in the identity column
3. Transaction B starts
4. Transaction B performs insert - This creates a new entry in the identity column
5. Transaction B commits
6. Your code performs its select and sees the identity value from the 2nd transaction
7. Transaction A commits -

The row inserted by Transaction A will never be found by your code. It was not already committed when step 6 was performed. And when the next query is performed it will not be found, because it has a lower value in the identity column than the query is looking for.

It could work if you perform the query with a read-uncommitted isolation mode