How Does SQL Server Generate Values In An Identity Column?

My question is if I have two batch inserts into one table in parallel, how does SQL Server create identity values? I mean, if in one session I insert multiple rows (Row1-Row2-Row3

Solution 1:

You are making the common fallacy of assuming an order in the table. Tables have no order. Only results have order, which is undetermined unless an explicit ORDER BY is specified.

You may ask a different question: how is the identity generated value assigned in case of concurrent inserts? The answer is simple: it doesn't matter. And if you make any assumption about the order then your code is broken. Same goes for gaps. Your application should work even if the identities generated are completely random, and correctly written application will work if the identity is completely random. Use SCOPE_IDENTITY() to retrieve the last inserted identity. Better still, use the OUTPUT clause of INSERT, it works for multi-row inserts too.

For the record: the identities are generated in the order on which operations acquire access to the log stream.