IDENTITY 列中出現意外的空白
我正在嘗試生成從 1 開始並以 1 遞增的唯一採購訂單號。我有一個使用此腳本創建的 PONumber 表:
CREATE TABLE [dbo].[PONumbers] ( [PONumberPK] [int] IDENTITY(1,1) NOT NULL, [NewPONo] [bit] NOT NULL, [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(), CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC) );
以及使用此腳本創建的儲存過程:
CREATE PROCEDURE [dbo].[GetPONumber] AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1); SELECT SCOPE_IDENTITY() AS PONumber; END
在創建時,這工作正常。當儲存過程執行時,它從所需的數字開始並以 1 遞增。
奇怪的是,如果我關閉或休眠我的電腦,那麼下一次執行該程序時,序列已經提前了近 1000。
請參閱下面的結果:
可以看到,這個數字從 8 躍升到了 1002!
為什麼會這樣?
我如何確保不會像那樣跳過數字?
我所需要的只是讓 SQL 生成以下數字:
- a) 保證獨一無二。
- b) 增加所需的數量。
我承認我不是 SQL 專家。我是否誤解了 SCOPE_IDENTITY() 的作用?我應該使用不同的方法嗎?我查看了 SQL 2012+ 中的序列,但微軟表示預設情況下不能保證它們是唯一的。
這是一個已知和預期的問題 - SQL Server 管理 IDENTITY 列的方式在 SQL Server 2012 中發生了變化(一些背景);預設情況下,它將記憶體 1000 個值,如果您重新啟動 SQL Server、重新啟動伺服器、故障轉移等,它將不得不丟棄這 1000 個值,因為它沒有可靠的方法來知道其中有多少是實際的發布。這在此處記錄。有一個跟踪標誌可以更改此行為,以便記錄每個 IDENTITY 分配*,防止那些特定的間隙(但不是回滾或刪除的間隙);但是,重要的是要注意,這在性能方面可能會非常昂貴,因此我什至不會在這裡提及特定的跟踪標誌。
*
(就我個人而言,我認為這是一個可以通過不同方式解決的技術問題,但由於我不編寫引擎,因此無法更改。)要清楚 IDENTITY 和 SEQUENCE 是如何工作的:
- 兩者都不能保證是唯一的(您需要在表級別強制使用主鍵或唯一約束)
- 兩者都不能保證是無間隙的(例如,任何回滾或刪除都會產生間隙,儘管存在這個特定問題)
唯一性很容易執行。避免差距不是。您需要確定避免這些間隙對您來說有多重要(理論上,您根本不應該關心間隙,因為 IDENTITY/SEQUENCE 值應該是無意義的代理鍵)。如果它非常重要,那麼您不應該使用任何一種實現,而應該使用您自己的可序列化序列生成器(請參閱此處、此處和此處的一些想法) - 請注意它會殺死並發性。
這個“問題”的很多背景: