SQL Server解惑——標識列的限制和跳號現象

 

1：每個表只能建立一個標識列。

 

如下測試所示，如果表中有一個標識列，新增一個標識列就會遇到錯誤“Multiple identity columns specified for table 'TEST'. Only one identity column per table is allowed.“

 

CREATE TABLE dbo.TEST
(
    ID        INT IDENTITY(1,1) ,
    NAME        VARCHAR(32)
);

ALTER TABLE dbo.TEST ADD  ID1 INT IDENTITY(10,1)

 

 

2：標識列不能被更新。

 

 

   如果你更新標識列，就會遇到類似下面這樣的錯誤。

 

   Cannot update identity column 'xxx'.

 

 

3：SQL Server不能通過ALTER語句修改標識列的increment值大小。

 

如果非要調整標識列的increment值大小，只能通過重建表來實現。如果想通過增加列或刪除列的方法，非常麻煩。很多情況下也是不行的。例如，有些情況下需要你對新增的自增標識列更新資料才能保證資料一致性。還有一種非常規方法就是修改系統基表sys.syscolpars。這個後續整理一篇。

 

 

4：SQL Server不能通過ALTER語句修改表標識列的SEED的大小。但是可以DBCC CHECKIDENT命令調整。SEED可以調大也可以調小，但是有一些限制！

 

#檢視某個表中的自增列當前的值：

DBCC CHECKIDENT (TableName,NORESEED)

 

#調整標識列的當前值（SEED）為50

DBCC CHECKIDENT('dbo.TEST', RESEED, 50);

 

通過DBCC CHECKIDENT命令調整SEED值大小，也是有限制的，如下實驗所示：

 

USE AdventureWorks2014;
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST')
BEGIN
    DROP TABLE test;
END
GO
CREATE TABLE dbo.TEST
(
    ID        INT IDENTITY(1,1) ,
    NAME    VARCHAR(32)
);

INSERT INTO dbo.TEST
        (  NAME )
SELECT  'K1' UNION ALL
SELECT  'K2' UNION ALL
SELECT  'K3' UNION ALL
SELECT  'K4' UNION ALL
SELECT  'K5' UNION ALL
SELECT  'K6';

SET IDENTITY_INSERT dbo.TEST ON;
GO
INSERT INTO dbo.TEST
        ( ID, NAME )
SELECT 13, 'k13';
GO
SET IDENTITY_INSERT dbo.TEST OFF;
GO

DBCC CHECKIDENT(test)

DBCC CHECKIDENT('test', RESEED ,9);



INSERT INTO dbo.TEST
        (  NAME )
SELECT  'K9'  UNION ALL
SELECT  'K10' UNION ALL
SELECT  'K11' UNION ALL
SELECT  'K12' UNION ALL
SELECT  'K13' ;
SELECT * FROM dbo.TEST;

 

如果你修改一下表結構，標識列為主鍵或有唯一約束的話，

 

CREATE TABLE dbo.TEST
(
    ID        INT IDENTITY(1,1) PRIMARY KEY,
    NAME      VARCHAR(32)
);

 

那麼上面指令碼執行到插入資料時就會報主鍵衝突。錯誤如下所示：

 

 

Msg 2627, Level 14, State 1, Line 38

Violation of PRIMARY KEY constraint 'PK__TEST__3214EC2731C41DF1'. Cannot insert duplicate key in object 'dbo.TEST'. The duplicate key value is (13).

 

 

那麼接下來，我們將上面的指令碼稍微調整一下，你會看到完全不同的結果。如下所示：

 

USE AdventureWorks2014;
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST')
BEGIN
    DROP TABLE test;
END
GO
CREATE TABLE dbo.TEST
(
    ID        INT IDENTITY(1,1) ,
    NAME      VARCHAR(32)
);

INSERT INTO dbo.TEST
        (  NAME )
SELECT  'K1' UNION ALL
SELECT  'K2' UNION ALL
SELECT  'K3' UNION ALL
SELECT  'K4' UNION ALL
SELECT  'K5' UNION ALL
SELECT  'K6';

SET IDENTITY_INSERT dbo.TEST ON;
GO
INSERT INTO dbo.TEST
        ( ID, NAME )
SELECT 13, 'k13';
GO
SET IDENTITY_INSERT dbo.TEST OFF;
GO



DBCC CHECKIDENT('test', RESEED ,9);
GO
DBCC CHECKIDENT(test);
GO

INSERT INTO dbo.TEST
        (  NAME )
SELECT  'K9'  UNION ALL
SELECT  'K10' UNION ALL
SELECT  'K11' UNION ALL
SELECT  'K12' UNION ALL
SELECT  'K13' ;
SELECT * FROM dbo.TEST;

 

這個是實驗測試時意外發現的一個問題，當時，它導致我得出不同的實驗結果，結論也搞錯了，問題出在DBCC CHECKIDENT (table_name)，如果表的當前標識值小於標識列中儲存的最大標識值，則使用標識列中的最大值對其進行重置。我使用DBCC CHECKIDENT(test)本意是來檢視標識列的當前值，所以正確的做法應該用DBCC CHECKIDENT(test, NORESEED)這條命令。其實這裡也衍生了一個問題，由於可以人為調整SEED的值，所以標識列的值的唯一性，必須通過“PRIMARY KEY”或“UNIQUE”約束或者通過“UNIQUE”索引來實現。將欄位設定為標識列並不能保證值的唯一值。

 

 

5： 不能通過ALTER語句將已經存在的一個欄位改為標識列

 

CREATE TABLE dbo.TEST
(
    ID        INT ,
    NAME      VARCHAR(32)
);

--這種語法是不允許的
ALTER TABLE dbo.TEST  ALTER COLUMN ID IDENTITY(10,1)

 

6：在記憶體優化表中，種子和增量必須分別設定為 1、1。 將種子或增量設定為 1 以外的值會導致以下錯誤：記憶體優化表不支援使用 1 以外的種子和增量值。另外，必須同時指定種子和增量，或者二者都不指定。 如果二者都未指定，則取預設值 (1,1)

 

 

7：如果事務回滾會導致標識列跳號。如下實驗所示，這種現象和Oracle、MySQL資料庫的行為一致。

 

--事務回滾導致標識列自增跳號
INSERT INTO dbo.TEST
        (  NAME )
SELECT  'K1' UNION ALL
SELECT  'K2' UNION ALL
SELECT  'K3' UNION ALL
SELECT  'K4' UNION ALL
SELECT  'K5' UNION ALL
SELECT  'K6';

BEGIN TRAN
INSERT INTO dbo.TEST
        (  NAME )
SELECT  'K7';
ROLLBACK TRAN;

INSERT INTO dbo.TEST
        (  NAME )
SELECT  'KKK';

SELECT * FROM dbo.TEST;

 

 

8: 事務內部，可能出現標識列的跳號。

 

 

如下實驗所示：

 

 

USE AdventureWorks2014;
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TRAN')
BEGIN
    DROP TABLE TEST_TRAN;
END
GO
CREATE TABLE dbo.TEST_TRAN
(
    ID        INT IDENTITY(1,1) PRIMARY KEY,
    TRN_NAME    VARCHAR(32)
);

 

在會話1和會話2同時執行下面SQL語句，模擬併發的事務。

 

 

 

--會話1：

 

DECLARE @row_index INT;

SET @row_index =1;

BEGIN TRAN
WHILE @row_index <=10
BEGIN
    INSERT INTO TEST_TRAN
    VALUES('TRANS_1');
    SET @row_index +=1;
    WAITFOR DELAY '00:00:01';
END
COMMIT TRAN;

 

--會話2

DECLARE @row_index INT;

SET @row_index =1;

BEGIN TRAN
WHILE @row_index <=10
BEGIN
    INSERT INTO TEST_TRAN
    VALUES('TRANS_2');
    SET @row_index +=1;

    WAITFOR DELAY '00:00:01';
END
COMMIT TRAN;

 

執行完上面指令碼後，我們可以看到在併發情況下，同一事務內可能出現跳號。這個可以稱其為“邏輯跳號”

 

 

 

 

 

9：資料庫例項非正常重啟（崩潰，故障轉移或關閉而導致SQL Server服務意外重啟），出現標識列的跳號

 

 

  關於這個，官方文件有簡單介紹。

 

Consecutive values after server restart or other failures -SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

 

  個人簡單測試了一下，發現在SQL Server 2012在伺服器非正常重啟（崩潰，故障轉移或關閉而導致SQL Server服務意外重啟）後會出現跳號（identity column jump）情況。可以通過啟用蹤標誌272解決這個問題（參考下面連結），SQL Server 2014下測試時，也是如此。注意：如果正常重啟SQL Server例項，並不會出現這種情況。這個跟ORACLE SEQUENCE跳號總結中的情況有點類似。

 

https://www.dfarber.com/computer-consulting-blog/articles/how-to-solve-identity-problem-in-sql-2012/

https://blog.sqlauthority.com/2017/03/24/sql-server-jump-identity-column-restart/

https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

https://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is

 

個人測試，在工作管理員，殺掉SQL Server的程式後，發現標識列跳號的大小為1000，根據上面部落格資料，標識列跳號的多少還跟標識列的資料型別有關。

 

 

不過在SQL Server 2017，引入了新特性IDENTITY_CACHE來解決這個問題！

 

 

按照網上搜尋的資料來看，蹤標誌272讓SQL Server使用以前的程式碼來實現標識列的功能。

 

That flag sets SQL 2012 back to the prior code for IDENTITY fields. However, unless you are actually running out of numbers, there is no reason to use that flag. IDENTITY fields are unique, not sequential. You probably need to rethink your method.

 

那麼我們想搞清楚標識列的下一個值儲存在哪裡呢？ SQL Server資料庫有個系統檢視sys.identity_columns可以檢視某個表的標識列的當前值和下一個值。

 

 

SELECT  name ,
        is_identity ,
        seed_value ,
        increment_value ,
        last_value
FROM    sys.identity_columns
WHERE   object_id = OBJECT_ID('TEST');

 

但是 sys.identity_columns是一個系統檢視，它的資料來自sys.syscolpars，而檢視的欄位last_value的值是通過內建函式IdentityProperty計算出來的

 

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW sys.identity_columns AS
    SELECT object_id = id,
        name = name,
        column_id = colid,
        system_type_id = xtype,
        user_type_id = utype,
        max_length = length,
        precision = prec,
        scale = scale,
        collation_name = convert(sysname,CollationPropertyFromId(collationid,'name')),
        is_nullable = sysconv(bit, 1 - (status & 1)),     -- CPM_NOTNULL
        is_ansi_padded = sysconv(bit, status & 2),     -- CPM_NOTRIM
        is_rowguidcol = sysconv(bit, status & 8),     -- CPM_ROWGUIDCOL
        is_identity = sysconv(bit, status & 4),         -- CPM_IDENTCOL
        is_filestream = sysconv(bit, status & 32),     -- CPM_FILESTREAM
        is_replicated = sysconv(bit, status & 0x20000),     -- CPM_REPLICAT
        is_non_sql_subscribed = sysconv(bit, status & 0x40000),     -- CPM_NONSQSSUB
        is_merge_published = sysconv(bit, status & 0x80000),         -- CPM_MERGEREPL
        is_dts_replicated = sysconv(bit, status & 0x100000),         -- CPM_REPLDTS
        is_xml_document = sysconv(bit, 0),
        xml_collection_id = sysconv(int, 0),
        default_object_id = sysconv(int, 0),
        rule_object_id = sysconv(int, 0),
        seed_value = IdentityProperty(id, 'SeedValue'),
        increment_value = IdentityProperty(id, 'IncrementValue'),
        last_value = IdentityProperty(id, 'LastValue'),
        is_not_for_replication = sysconv(bit, status & 0x10000),    -- CPM_ID_REPL
        is_computed = sysconv(bit, status & 16),            -- CPM_COMPUTED
        sysconv(bit, 0) as is_sparse,
        sysconv(bit, 0) as is_column_set
    FROM sys.syscolpars
    WHERE number = 0    -- SOC_COLUMN
        AND (status & 4) = 4     -- CPM_IDENTCOL
        AND has_access('CO', id) = 1

GO

 

 無法獲取系統內建函式（built-in function）的定義，所以無法進一步分析標識列是如何儲存last_value的，但是個人猜測可能跟系統基表sys.syscolpars的idtval欄位有關係。DAC模式下查詢跟蹤，你會發現標識列ID變化後，idtval欄位的值也變化了。

 

 

 

 

 

新建三個表，標識列的自增值分別為1、2、3, ，然後，查詢系統基表sys.syscolpars你就會有所發現，如下截圖所示

CREATE TABLE test1(id INT IDENTITY(1,1), name VARCHAR(10))
CREATE TABLE test2(id INT IDENTITY(1,2), name VARCHAR(10))
CREATE TABLE test3(id INT IDENTITY(1,3), name VARCHAR(10))

 

 

 

10：TRUNCATE表後，標識列的當前值會變為1

 

 

11：與標識列相關的系統函式的區別。

 

 

SELECT IDENT_CURRENT('dbo.TEST_TRAN');

SELECT IDENT_INCR('dbo.TEST_TRAN');

SELECT IDENT_SEED('dbo.TEST_TRAN')

SELECT SCOPE_IDENTITY();

SELECT @@IDENTITY;

 

 

IDENT_CURRENT 類似於SQL Server 2000 (8.x)的標識函式 SCOPE_IDENTITY 和 @@IDENTITY。 這三個函式都返回最後生成的標識值。 但是，上述每個函式中定義的“最後”的作用域和會話有所不同**：

·         IDENT_CURRENT 返回為某個會話和用域中的指定表生成的最新標識值。

·         @@IDENTITY 返回為跨所有作用域的當前會話中的任何表生成的最後一個標識值。

·         SCOPE_IDENTITY 返回為當前會話和當前作用域中的某個表生成的最新標識值。

如果 IDENT_CURRENT 值為 NULL（因為表從未包含行或已被截斷），IDENT_CURRENT 函式將返回種子值。

 

 

參考資料：

 

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15

https://www.dfarber.com/computer-consulting-blog/articles/how-to-solve-identity-problem-in-sql-2012/

https://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is