1：每個(gè)表只能創(chuàng)建一個(gè)標(biāo)識(shí)列。
如下測(cè)試所示，如果表中有一個(gè)標(biāo)識(shí)列，新增一個(gè)標(biāo)識(shí)列就會(huì)遇到錯(cuò)誤“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：標(biāo)識(shí)列不能被更新。
如果你更新標(biāo)識(shí)列，就會(huì)遇到類似下面這樣的錯(cuò)誤。
Cannot update identity column 'xxx'.
3：SQL Server不能通過ALTER語句修改標(biāo)識(shí)列的increment值大小。
如果非要調(diào)整標(biāo)識(shí)列的increment值大小，只能通過重建表來實(shí)現(xiàn)。如果想通過增加列或刪除列的方法，非常麻煩。很多情況下也是不行的。例如，有些情況下需要你對(duì)新增的自增標(biāo)識(shí)列更新數(shù)據(jù)才能保證數(shù)據(jù)一致性。還有一種非常規(guī)方法就是修改系統(tǒng)基表sys.syscolpars。這個(gè)后續(xù)整理一篇。
4：SQL Server不能通過ALTER語句修改表標(biāo)識(shí)列的SEED的大小。但是可以DBCC CHECKIDENT命令調(diào)整。SEED可以調(diào)大也可以調(diào)小，但是有一些限制！
#查看某個(gè)表中的自增列當(dāng)前的值：
DBCC CHECKIDENT (TableName,NORESEED)
#調(diào)整標(biāo)識(shí)列的當(dāng)前值（SEED）為50
DBCC CHECKIDENT('dbo.TEST', RESEED, 50);
通過DBCC CHECKIDENT命令調(diào)整SEED值大小，也是有限制的，如下實(shí)驗(yàn)所示：
USE AdventureWorks2014;GOIF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST')
BEGINDROP TABLE test;
ENDGOCREATE 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;
GOINSERT INTO dbo.TEST( ID, NAME )
SELECT 13, 'k13';
GOSET IDENTITY_INSERT dbo.TEST OFF;
GODBCC 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;
如果你修改一下表結(jié)構(gòu)，標(biāo)識(shí)列為主鍵或有唯一約束的話，
CREATE TABLE dbo.TEST
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(32));
那么上面腳本運(yùn)行到插入數(shù)據(jù)時(shí)就會(huì)報(bào)主鍵沖突。錯(cuò)誤如下所示：
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).
那么接下來，我們將上面的腳本稍微調(diào)整一下，你會(huì)看到完全不同的結(jié)果。如下所示：
USE AdventureWorks2014;GOIF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST')
BEGINDROP TABLE test;
ENDGOCREATE 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;
GOINSERT INTO dbo.TEST( ID, NAME )
SELECT 13, 'k13';
GOSET IDENTITY_INSERT dbo.TEST OFF;
GODBCC CHECKIDENT('test', RESEED ,9);
GODBCC CHECKIDENT(test);GOINSERT 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;
這個(gè)是實(shí)驗(yàn)測(cè)試時(shí)意外發(fā)現(xiàn)的一個(gè)問題，當(dāng)時(shí)，它導(dǎo)致我得出不同的實(shí)驗(yàn)結(jié)果，結(jié)論也搞錯(cuò)了，問題出在DBCC CHECKIDENT (table_name)，如果表的當(dāng)前標(biāo)識(shí)值小于標(biāo)識(shí)列中存儲(chǔ)的最大標(biāo)識(shí)值，則使用標(biāo)識(shí)列中的最大值對(duì)其進(jìn)行重置。我使用DBCC CHECKIDENT(test)本意是來查看標(biāo)識(shí)列的當(dāng)前值，所以正確的做法應(yīng)該用DBCC CHECKIDENT(test, NORESEED)這條命令。其實(shí)這里也衍生了一個(gè)問題，由于可以人為調(diào)整SEED的值，所以標(biāo)識(shí)列的值的唯一性，必須通過“PRIMARY KEY”或“UNIQUE”約束或者通過“UNIQUE”索引來實(shí)現(xiàn)。將字段設(shè)置為標(biāo)識(shí)列并不能保證值的唯一值。
5： 不能通過ALTER語句將已經(jīng)存在的一個(gè)字段改為標(biāo)識(shí)列
CREATE TABLE dbo.TEST
(
ID INT , NAME VARCHAR(32));
--這種語法是不允許的
ALTER TABLE dbo.TEST ALTER COLUMN ID IDENTITY(10,1)
6：在內(nèi)存優(yōu)化表中，種子和增量必須分別設(shè)置為 1、1。 將種子或增量設(shè)置為 1 以外的值會(huì)導(dǎo)致以下錯(cuò)誤：內(nèi)存優(yōu)化表不支持使用 1 以外的種子和增量值。另外，必須同時(shí)指定種子和增量，或者二者都不指定。 如果二者都未指定，則取默認(rèn)值 (1,1)
7：如果事務(wù)回滾會(huì)導(dǎo)致標(biāo)識(shí)列跳號(hào)。如下實(shí)驗(yàn)所示，這種現(xiàn)象和Oracle、MySQL數(shù)據(jù)庫的行為一致。
--事務(wù)回滾導(dǎo)致標(biāo)識(shí)列自增跳號(hào)
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: 事務(wù)內(nèi)部，可能出現(xiàn)標(biāo)識(shí)列的跳號(hào)。
如下實(shí)驗(yàn)所示：
USE AdventureWorks2014;GOIF EXISTS (SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TRAN')
BEGINDROP TABLE TEST_TRAN;
ENDGOCREATE TABLE dbo.TEST_TRAN
(
ID INT IDENTITY(1,1) PRIMARY KEY,
TRN_NAME VARCHAR(32));
在會(huì)話1和會(huì)話2同時(shí)執(zhí)行下面SQL語句，模擬并發(fā)的事務(wù)。
--會(huì)話1：
DECLARE @row_index INT;
SET @row_index =1;BEGIN TRAN
WHILE @row_index <=10BEGIN INSERT INTO TEST_TRANVALUES('TRANS_1');
SET @row_index +=1;WAITFOR DELAY '00:00:01';
ENDCOMMIT TRAN;
--會(huì)話2
DECLARE @row_index INT;
SET @row_index =1;BEGIN TRAN
WHILE @row_index <=10BEGIN INSERT INTO TEST_TRANVALUES('TRANS_2');
SET @row_index +=1;WAITFOR DELAY '00:00:01';
ENDCOMMIT TRAN;
執(zhí)行完上面腳本后，我們可以看到在并發(fā)情況下，同一事務(wù)內(nèi)可能出現(xiàn)跳號(hào)。這個(gè)可以稱其為“邏輯跳號(hào)”
9：數(shù)據(jù)庫實(shí)例非正常重啟（崩潰，故障轉(zhuǎn)移或關(guān)閉而導(dǎo)致SQL Server服務(wù)意外重啟），出現(xiàn)標(biāo)識(shí)列的跳號(hào)
關(guān)于這個(gè)，官方文檔有簡單介紹。
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.
個(gè)人簡單測(cè)試了一下，發(fā)現(xiàn)在SQL Server 2012在服務(wù)器非正常重啟（崩潰，故障轉(zhuǎn)移或關(guān)閉而導(dǎo)致SQL Server服務(wù)意外重啟）后會(huì)出現(xiàn)跳號(hào)（identity column jump）情況?？梢酝ㄟ^啟用蹤標(biāo)志272解決這個(gè)問題（參考下面鏈接），SQL Server 2014下測(cè)試時(shí)，也是如此。注意：如果正常重啟SQL Server實(shí)例，并不會(huì)出現(xiàn)這種情況。這個(gè)跟ORACLE SEQUENCE跳號(hào)總結(jié)中的情況有點(diǎn)類似。
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
個(gè)人測(cè)試，在任務(wù)管理器，殺掉SQL Server的進(jìn)程后，發(fā)現(xiàn)標(biāo)識(shí)列跳號(hào)的大小為1000，根據(jù)上面博客資料，標(biāo)識(shí)列跳號(hào)的多少還跟標(biāo)識(shí)列的數(shù)據(jù)類型有關(guān)。
不過在SQL Server 2017，引入了新特性IDENTITY_CACHE來解決這個(gè)問題！
按照網(wǎng)上搜索的資料來看，蹤標(biāo)志272讓SQL Server使用以前的代碼來實(shí)現(xiàn)標(biāo)識(shí)列的功能。
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.
那么我們想搞清楚標(biāo)識(shí)列的下一個(gè)值保存在哪里呢？ SQL Server數(shù)據(jù)庫有個(gè)系統(tǒng)視圖sys.identity_columns可以查看某個(gè)表的標(biāo)識(shí)列的當(dāng)前值和下一個(gè)值。
SELECT name ,is_identity ,
seed_value ,
increment_value ,
last_value
FROM sys.identity_columnsWHERE object_id = OBJECT_ID('TEST');
但是 sys.identity_columns是一個(gè)系統(tǒng)視圖，它的數(shù)據(jù)來自sys.syscolpars，而視圖的字段last_value的值是通過內(nèi)置函數(shù)IdentityProperty計(jì)算出來的
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GOCREATE 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_IDENTCOLAND has_access('CO', id) = 1
GO
無法獲取系統(tǒng)內(nèi)置函數(shù)（built-in function）的定義，所以無法進(jìn)一步分析標(biāo)識(shí)列是如何保存last_value的，但是個(gè)人猜測(cè)可能跟系統(tǒng)基表sys.syscolpars的idtval字段有關(guān)系。DAC模式下查詢跟蹤，你會(huì)發(fā)現(xiàn)標(biāo)識(shí)列ID變化后，idtval字段的值也變化了。
新建三個(gè)表，標(biāo)識(shí)列的自增值分別為1、2、3, ，然后，查詢系統(tǒng)基表sys.syscolpars你就會(huì)有所發(fā)現(xiàn)，如下截圖所示
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表后，標(biāo)識(shí)列的當(dāng)前值會(huì)變?yōu)?
11：與標(biāo)識(shí)列相關(guān)的系統(tǒng)函數(shù)的區(qū)別。
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)的標(biāo)識(shí)函數(shù) SCOPE_IDENTITY 和 @@IDENTITY。 這三個(gè)函數(shù)都返回最后生成的標(biāo)識(shí)值。 但是，上述每個(gè)函數(shù)中定義的“最后”的作用域和會(huì)話有所不同**：
· IDENT_CURRENT 返回為某個(gè)會(huì)話和用域中的指定表生成的最新標(biāo)識(shí)值。
· @@IDENTITY 返回為跨所有作用域的當(dāng)前會(huì)話中的任何表生成的最后一個(gè)標(biāo)識(shí)值。
· SCOPE_IDENTITY 返回為當(dāng)前會(huì)話和當(dāng)前作用域中的某個(gè)表生成的最新標(biāo)識(shí)值。
如果 IDENT_CURRENT 值為 NULL（因?yàn)楸韽奈窗谢蛞驯唤財(cái)啵?span lang="X-NONE">IDENT_CURRENT 函數(shù)將返回種子值。
參考資料：
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
聯(lián)系客服
