MS SQL Identity 欄位數值跳號問題探討

2015-09-04

7182
0
[SQL]Lab

關於MS SQL Identity欄位數值跳號問題之前就有發現，當時微軟MVP Terry有詢問微軟這個問題。

微軟給的回覆是Identity欄位不保證連號，在某些情況下會造成跳號問題。而近期在FB也有社團朋

友提出一樣的問題，因此自己做了一下LAB並記錄一下。

目前我知道會造成跳號的情況如下（如果您知道還有其他狀況會造成跳號，歡迎分享給我）

１．交易已取號，但後來Rollback。

２．重啟SQL服務。

３．Restore資料庫。

而這一次我測試了4個SQL版本(SQL2008R2、SQL2012、SQL2014、SQL2016CTP2)，看看不同

版本是否有差異性。

情境一 : 交易已取號，但後來Rollback。

測試語法如下

Use [master]
GO
Create Database RockDB;
GO
Use [RockDB]
GO
Create Table Tb(Id INT Identity,Name Char(4));
GO
Insert Into Tb Values('Rock');
GO
Begin Tran
Insert Into Tb Values('Rock');
Rollback
GO
Insert Into Tb Values('Rock');
GO
Select *,left(@@version,25) AS DbVersion From Tb;
GO

測試結果 : 我們在4個版本的SQL上執行上面的語法，結果如下。我們透過測試結果可以知道，

交易的Rollback在4個SQL版本都會造成跳號。

情境二 : 重啟SQL服務。測試方法如下

(1)我們先執行下面語法，產生相關表格並塞入資料

Use [master]
GO
Create Database RockDB;
GO
Use [RockDB]
GO
Create Table Tb(Id INT Identity,Name Char(4));
GO
Insert Into Tb Values('Rock');
GO
Insert Into Tb Values('Rock');
GO

(2)如下圖所示，重啟SQL服務

(3)完成重啟服務後再次塞入資料到資料表中

測試結果 : 上述為測試步驟，結果如下。我們可以發現SQL2012在SQL服務重啟後會造成跳號，

而且一次就增加1000號。這是SQL2012的已知問題，已在SQL2014及SQL2016CTP2就修正了

這一個問題。

情境三 : 還原資料庫。

執行語法如下

Use [master]
GO
Create Database RockDB;
GO
Use [RockDB]
GO
Create Table Tb(Id INT Identity,Name Char(4));
GO
Insert Into Tb Values('Rock');
GO
Backup Database RockDB To Disk=N'E:\SQLBak\RockDB.bak'
GO
Insert Into Tb Values('Rock');
GO
Backup Log RockDB To Disk=N'E:\SQLBak\RockDB.trn'
GO
Insert Into Tb Values('Rock');
GO
Use [master]
Go
Backup Log RockDB To Disk=N'E:\SQLBak\RockDB_Taillog.trn' With NoRecovery;
Restore Database RockDB From Disk=N'E:\SQLBak\RockDB.bak' With NoRecovery;
Restore Log RockDB From Disk=N'E:\SQLBak\RockDB.trn' With Recovery;
GO
Use [RockDB]
GO
Insert Into Tb Values('Rock');
GO
Select *,left(@@version,25) AS DbVersion From Tb;
GO

測試結果 : 上述為測試步驟，結果如下。我們可以發現只有SQL2008R2在還原資料庫後不

會有跳號情況，在SQL2012 UP版本的SQL，在資料庫還原後再塞入資料都會有跳1000號

的情況產生。

完成上述所有測試後，簡易的列表一下

	SQL2008R2	SQL2012	SQL2014	SQL2016CTP2
交易Rollback	跳號	跳號	跳號	跳號
重啟SQL服務		跳號
還原資料庫		跳號	跳號	跳號