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.