SQL Server自增列跳號總結

潇湘隐者發表於2024-03-08
SQLServer

從SQL Server 2012開始，Microsoft就引入了一個新的功能/特性IDENTITY CACHE，引入這個功能的目錄是為了提高包含自增列(IDENTITY)相關表的INSERT效能。但是隨之而來的，就是可能會出現自增列(標識列)的跳號問題，如下所示：

CREATE TABLE TEST(ID INT IDENTITY(1,1), NAME VARCHAR(16));

INSERT INTO TEST(NAME)
SELECT 'Kerry1' UNION ALL
SELECT 'Kerry2';

SELECT * FROM TEST;

然後，我們透過Windows工作管理員，殺掉SQL Server服務的程序，模擬SQL Server Abort或伺服器意外重新啟動的情況。或者你使用下面命令關閉資料庫例項

SHUTDOWN WITH NOWAIT

我們重啟資料庫例項後，繼續插入資料，你會發現欄位ID跳號了，如下截圖所示：

INSERT INTO TEST(NAME)
SELECT 'Kerry3';

自增列跳號的差值(GAP)

從上面的測試實驗，我們看到自增列ID的跳號的差值(GAP)為1000，那麼有一個疑問，所有的自增列跳號的差值（GAP）都是1000嗎？ 答案是No，這個跳號的差值(GAP)跟自增列的資料型別有關，如果自增列欄位型別為INT型別的話，差值(GAP)為1000，如果自增列為BIGINT或Numeric資料型別的話，跳號(GAP)為10000，這個有興趣的話，可以自行測試一下。這裡就不展開敘述了。

另外，生產環境自增列跳號的值有可能小於1000或10000，不一定完全是1000或10000，你可以模擬這種情況，如下所示，在測試環境迴圈插入資料，然後模擬異常重啟。

DECLARE @insert_rows INT =0
DECLARE @row_count INT=100000

WHILE @insert_rows < @row_count
BEGIN
INSERT INTO TEST(NAME)
SELECT 'KADFADFIAT'

 SET @insert_rows =@insert_rows+1
END;

因為官方文件缺少相關資料，對其內部原理所知甚少，所以很難準確描述為什麼跳號會小於1000或10000，我們只需知道有這種現象。我曾絞盡腦汁設想一些可能性，但是都缺乏證據支援。

什麼時候出現自增列跳號？

什麼場景會出現自增列跳號呢？ SQL Server 由於出於效能原因而快取自增列的值，在資料庫故障或伺服器重新啟動期間，一些分配的值可能丟失。 這可能導致在插入時自增列值（標識值)之間有間隔。具體來說，分為下面幾種情況：

  • 1： SQL Server奔潰或伺服器意外重啟（例如，伺服器當機、藍色畫面、SQL Server例項Crash、SHUTDOWN WITH NOWAIT）
  • 2： AlwaysON的故障轉移時出現自增列跳號。如果正常的重啟SQL Server服務/例項是不會導致自增列跳號的。至於原因和內部原理，沒有相關的官方解釋，但是參考ORACLE SEQUENCE跳號總結[1]，大體原理估計也差不多。

如何禁止自增列跳號

1：跟蹤標記禁用IDENTITY CACHE

在早期的SQL Server版本，例如，SQL Server 2012、2014等版本，由於一些業務邏輯的要求，你要杜絕/禁止自增列跳號的話，必須透過跟蹤標記272來禁用IDENTITY CACHE，避免在伺服器意外重新啟動(Abort/當機)或故障轉移到輔助伺服器的情況下，禁用自增列預分配以避免標識列的值出現跳號的情況，這個是在全域性/實伺服器級別設定。如下所示：

2：使用序列替換自增列

如果不允許有間隔，應用程式應使用自己的機制來生成鍵值。使用沒有CACHE的序列替換自增列。將序列生成器與 NOCACHE 選項結合使用可以限制從未提交的事務的間隔。

3：在資料庫級別禁用IDENTITY CACHE

從SQL Server 2017開始，Micosoft提供提供了一個新功能，允許你在資料庫級別禁用或啟用標識快取(IDENTITY CACHE)。如下所示

USE <YourSQLDB>
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO

切換到指定的資料庫，執行下面第二條SQL就能開啟IDENTITY CACHE，如果想關閉資料庫的IDENTITY CACHE功能的話，就執行下面SQL

USE <YourSQLDba>
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

查詢開啟還是關閉了IDENTITY CACHE,可以透過下面語句檢視

USE <YourSQLDba>
GO
SELECT * FROM sys.database_scoped_configurations WHERE NAME='IDENTITY_CACHE'

參考資料

[1]

1: https://www.cnblogs.com/kerrycode/p/7461180.html

相關文章