問題描述

我的問題是，如果我有兩個并行的批量插入到一個表中，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.

這篇關于SQL Server 如何在標識列中生成值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！