Sql-Server

IDENTITY 列中出現意外的空白

  • September 11, 2019

我正在嘗試生成從 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 值應該是無意義的代理鍵)。如果它非常重要,那麼您不應該使用任何一種實現,而應該使用您自己的可序列化序列生成器(請參閱此處此處此處的一些想法) - 請注意它會殺死並發性。

這個“問題”的很多背景:

引用自:https://dba.stackexchange.com/questions/106209