SQL Server 如何在标识列中生成值?

问题描述

我的问题是，如果我有两个并行的批量插入到一个表中，SQL Server 如何创建标识值?

My question is if I have two batch inserts into one table in parallel, how does SQL Server create identity values?

我的意思是，如果在一个会话中我插入多行(Row1-Row2-Row3)，同时另一个会话同时插入多行(Row4-Row5-Row6)，结果会是这样吗?

I mean, if in one session I insert multiple rows (Row1-Row2-Row3) and simultaneously another session inserts multiple rows (Row4-Row5-Row6) at the same time, the result would be like this?

Row1
Row2
Row3
Row4
Row5
Row6

或者类似的东西?

Row1
Row6
Row3
Row5
Row4
Row2

推荐答案

您犯了假定表格中的顺序的常见谬误.表没有顺序.只有结果才有顺序，除非指定了明确的 ORDER BY，否则顺序是不确定的.

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.

您可能会问一个不同的问题:在并发插入的情况下，如何分配标识生成的值?答案很简单:没关系.如果您对订单做出任何假设，那么您的代码就会被破坏.间隙也是如此.即使生成的身份完全随机，您的应用程序也应该可以运行，如果身份完全随机，正确编写的应用程序将运行.使用 SCOPE_IDENTITY() 检索最后一个插入的身份.更好的是，使用 OUTPUT 子句INSERT，它也适用于多行插入.

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.