從SQL Server 2012開(kāi)始有了Sequence，簡(jiǎn)單用列如下：

CREATE SEQUENCE TestSeqSTART WITH 1INCREMENT BY 1 ;SELECT NEXT VALUE FOR TestSeq AS NextValue;

在這之前，表中生成序列號(hào)大多都是借助IDENTITY列屬性，當(dāng)然也有一些時(shí)候，是在自定義表中，自己維護(hù)序列號(hào)。

一. 創(chuàng)建IDENTITY列

if OBJECT_ID('test','U') is not null    drop table testGOcreate table test(id int identity, c1 char(1))insert test values('a');insert test values('b');select * from test

1. 沒(méi)有指定IDENTITY(seed ,increment)，默認(rèn)就是 IDENTITY(1, 1)，效果同如下語(yǔ)句

create table test(id int identity(1,1), c1 char(1))

2. 通過(guò)函數(shù)或者系統(tǒng)視圖，都可以查看是否為IDENTITY列

SELECT COLUMNPROPERTY(OBJECT_ID('test'),'id','IsIdentity') AS is_identityselect object_name(object_id) as table_name, is_identity,* from sys.columns where object_id=object_id('test') --and is_identity=1

3. 重置IDENTITY列的初始值，通常在數(shù)據(jù)刪除/歸檔后進(jìn)行

DELETE testDBCC CHECKIDENT('test', RESEED, 1)DBCC CHECKIDENT('test', NORESEED)--TRUNCATE表后會(huì)自動(dòng)重置IDENTITY列TRUNCATE TABLE testDBCC CHECKIDENT('test', NORESEED)

二. 獲取IDENTITY列值插入了數(shù)據(jù)，有時(shí)還需要獲取剛才生成的序列值另作他用，返回給前端也好，或者插入其他將來(lái)需要關(guān)聯(lián)的表。

記得曾經(jīng)有個(gè)面試題：假設(shè)當(dāng)前表IDENTITY列最大值為N，在存儲(chǔ)過(guò)程中，對(duì)這個(gè)表插入1行數(shù)據(jù)，獲取到的IDENTITY列值有時(shí)小于或者大于N+1，可能是什么原因？

獲取IDENTITY列值有三種方式：(1) IDENT_CURRENT( 'table_name' ) 返回為任何會(huì)話和任何作用域中的特定表最后生成的標(biāo)識(shí)值。(2) @@IDENTITY 返回為當(dāng)前會(huì)話的所有作用域中的任何表最后生成的標(biāo)識(shí)值。(3) SCOPE_IDENTITY() 返回為當(dāng)前會(huì)話和當(dāng)前作用域中的任何表最后生成的標(biāo)識(shí)值。

IDENT_CURRENT( 'table_name' ) 針對(duì)特定表，是全局的。@@IDENTITY和SCOPE_IDENTITY()針對(duì)所有表，區(qū)別在于作用域，也就是上下文：(1) 如果當(dāng)前INSERT語(yǔ)句上有函數(shù)，觸發(fā)器等(不同作用域的)對(duì)象返回的IDENTITY值，那么@@IDENTITY會(huì)取所有表上的最后1個(gè)，而不是當(dāng)前表上的；

(2) SCOPE_IDENTITY()會(huì)取當(dāng)前作用域所有表上最后1個(gè)IDENTITY值，被調(diào)用的函數(shù)，觸發(fā)器已經(jīng)超出了作用域/上下文。所以在使用INSERT后，接著使用SCOPE_IDENTITY()獲取IDENTITY列值，就不會(huì)有問(wèn)題了：

insert test values('z');select SCOPE_IDENTITY() as curr_value

一個(gè)GO語(yǔ)句/批處理，也是一個(gè)上下文的分界點(diǎn)，但是SQL語(yǔ)句是順序執(zhí)行的，所以一個(gè)會(huì)話里，只要在INSERT之后用SCOPE_IDENTITY()來(lái)獲取IDENTITY值是沒(méi)問(wèn)題的。

三. 修改IDENTITY列值/屬性1. 對(duì)已存在的列增加/刪除IDENTITY屬性

if OBJECT_ID('t_id') is not nulldrop table t_idGOcreate table t_id(id int,c1 char(1))insert into t_idselect 1,'a' union allselect 2,'b'alter table t_id alter column id int identity(1,2)/*Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyWord 'identity'.*/

直接修改列屬性會(huì)報(bào)錯(cuò)，IDENTITY屬性只能伴隨著列增加/刪除。

(1) 利用中間表在SSMS界面上設(shè)計(jì)表(SSMS/Tables/Design)，可以直接增加/刪除列上的IDENTITY屬性，如果生成腳本看看的話(右擊編輯框/工具欄/菜單欄)，可以發(fā)現(xiàn)SSMS是利用了中間表，并非在原表直接修改屬性。

表上有約束，索引等對(duì)象時(shí)，腳本會(huì)更加繁雜些。示例如下圖：

如果出現(xiàn)如下錯(cuò)誤：Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

是因?yàn)镾SMS里有個(gè)選項(xiàng)沒(méi)設(shè)置，SQL Server認(rèn)為有刪除/重建表的腳本不安全，所以默認(rèn)關(guān)閉了，需要手動(dòng)開(kāi)啟一下，去掉那個(gè)勾：

對(duì)表上已存在列添加IDENTITY屬性，生成的腳本如下：

BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONGOCREATE TABLE dbo.Tmp_t_id    (    id int NOT NULL IDENTITY (1, 1),    c1 char(1) NULL    )  ON [PRIMARY]GOALTER TABLE dbo.Tmp_t_id SET (LOCK_ESCALATION = TABLE)GOSET IDENTITY_INSERT dbo.Tmp_t_id ONGOIF EXISTS(SELECT * FROM dbo.t_id)     EXEC('INSERT INTO dbo.Tmp_t_id (id, c1)        SELECT id, c1 FROM dbo.t_id WITH (HOLDLOCK TABLOCKX)')GOSET IDENTITY_INSERT dbo.Tmp_t_id OFFGODROP TABLE dbo.t_idGOEXECUTE sp_rename N'dbo.Tmp_t_id', N't_id', 'OBJECT' GOCOMMIT

對(duì)表上已存在列刪除IDENTITY屬性，生成的腳本如下：

BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONGOCREATE TABLE dbo.Tmp_t_id    (    id int NOT NULL,    c1 char(1) NULL    )  ON [PRIMARY]GOALTER TABLE dbo.Tmp_t_id SET (LOCK_ESCALATION = TABLE)GOIF EXISTS(SELECT * FROM dbo.t_id)     EXEC('INSERT INTO dbo.Tmp_t_id (id, c1)        SELECT id, c1 FROM dbo.t_id WITH (HOLDLOCK TABLOCKX)')GODROP TABLE dbo.t_idGOEXECUTE sp_rename N'dbo.Tmp_t_id', N't_id', 'OBJECT' GOCOMMIT

(2) 利用中間列對(duì)表上已存在列刪除IDENTITY屬性

if OBJECT_ID('t_id') is not null    drop table t_idGOcreate table t_id(id int identity(1,1),c1 char(1))insert into t_idselect 'a' union allselect 'b'select * from t_idSELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')--在表上新增一個(gè)列，把IDENTITY列值復(fù)制過(guò)去alter table t_id add id_new intGOupdate t_id set id_new = id--刪除原來(lái)的列，并重命名新增列alter table t_id drop column idexec sp_rename 't_id.id_new','id'select * from t_idSELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')

對(duì)表上已存在列添加IDENTITY屬性，用中間列的方式不太可行，因?yàn)?strong>IDENTITY列不接受UPDATE，新增的IDENTITY列無(wú)法直接復(fù)制原id的值，還得借助中間表，但如果不需要原來(lái)id的值，那么可以：

if OBJECT_ID('t_id') is not null    drop table t_idGOcreate table t_id(id int,c1 char(1))insert into t_idselect 1,'a' union allselect 3,'b'select * from t_idSELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')--在表上新增一個(gè)IDENTITY列，不復(fù)制原來(lái)的ID值alter table t_id add id_new int identity(1,1) not null --刪除原來(lái)的列，并重命名新增列alter table t_id drop column idexec sp_rename 't_id.id_new','id'select * from t_idSELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')

2. 在IDENTITY列上做增刪改操作(DML)(1) 刪除操作沒(méi)有問(wèn)題，直接DELETE即可

delete test where id = 2

(2) 如果要顯式INSERT某個(gè)值，需要開(kāi)啟IDENTITY_INSERT這個(gè)session級(jí)的選項(xiàng)

set IDENTITY_INSERT test on;insert test(id,c1) values(3,'c');set IDENTITY_INSERT test off;select * from test

(3) 如果要UPDATE IDENTITY列值，無(wú)論是否開(kāi)啟IDENTITY_INSERT這個(gè)選項(xiàng)都無(wú)法更新

set IDENTITY_INSERT test on;update test set id = 10 where id = 1set IDENTITY_INSERT test off;/*Msg 8102, Level 16, State 1, Line 1Cannot update identity column 'id'.*/

非要修改的話，就得借助中間表，在不含IDENTITY屬性的中間表里做完UPDATE，然后再把數(shù)據(jù)導(dǎo)回來(lái)。中間表可參考上面的腳本。

3. IDENTITY列屬性復(fù)制(1) 直接從單表SELECT INTO table_name，原表其他約束，索引等等都不會(huì)被復(fù)制，但是IDENTITY屬性會(huì)被復(fù)制。

select * into test2 from testselect * from test2select columnproperty(OBJECT_ID('test'),'id','IsIdentity')select columnproperty(OBJECT_ID('test2'),'id','IsIdentity')

(2) 如果有IDENTITY屬性的表和其他表JOIN，那么IDENTITY屬性不會(huì)被復(fù)制。

select a.* into test3 from test a inner join sys.objects bon a.id = b.object_idselect * from test3select columnproperty(OBJECT_ID('test3'),'id','IsIdentity')

假如復(fù)制表時(shí)，不想要IDENTITY屬性，正好可以利用一下這個(gè)特點(diǎn)，如下：

select a.* into test4from test a inner join sys.objects bon 1=2

(3) 如果用SELECT INTO table_name導(dǎo)數(shù)據(jù)時(shí)，F(xiàn)ROM子句有多表關(guān)聯(lián)，且想要保留IDENTITY屬性，這時(shí)可以用INSERT，并考慮使用TABLOCK提示

if OBJECT_ID('test5','U') is not nulldrop table test5GOcreate table test5(id int identity, c1 char(1))select * from test5GOset IDENTITY_INSERT test5 on;insert into test5 WITH(TABLOCK) (id,c1)select a.* from test a inner join test2 b on a.id = b.idset IDENTITY_INSERT test5 off;select * from test5select columnproperty(OBJECT_ID('test5'),'id','IsIdentity')

這里使用了WITH(TABLOCK)選項(xiàng)，在SIMPLE或者BULK_LOGGED恢復(fù)模式下，SELECT…INTO table_name和INSERT INTO table_name WITH(TABLOCK)都能最小化日志。

4. 借助SWITCH來(lái)處理IDENTITY屬性，推薦同樣也是利用中間表，上面的幾個(gè)列子都使用了INSERT，這里使用SWITCH，不再有數(shù)據(jù)倒來(lái)倒去的開(kāi)銷，需要SQL Server 2008及以上版本，能比較有效地同時(shí)解決上面的3個(gè)問(wèn)題：(1) 不能直接對(duì)表上現(xiàn)有列增加/刪除IDENTITY屬性；(2) 不能直接更新IDENTITY列；(3) 復(fù)制表時(shí)，有選擇的復(fù)制IDENTITY列屬性(多表關(guān)聯(lián)，對(duì)關(guān)聯(lián)后的表做SWITCH以實(shí)現(xiàn))；

CREATE TABLE Temp1(ID INT IDENTITY(1,1) PRIMARY KEY,X VARCHAR(10))INSERT INTO Temp1 OUTPUT INSERTED.*SELECT 'Foo' UNION ALLSELECT 'Bar' UNION ALLSELECT 'Baz'CREATE TABLE Temp2(ID INT PRIMARY KEY,X VARCHAR(10))ALTER TABLE Temp1 SWITCH TO Temp2;SELECT COLUMNPROPERTY(OBJECT_ID('Temp1'),'id','IsIdentity')SELECT COLUMNPROPERTY(OBJECT_ID('Temp2'),'id','IsIdentity')INSERT INTO Temp2OUTPUT INSERTED.*SELECT 10,'Foo' UNION ALLSELECT 20,'Bar' UNION ALLSELECT 5, 'Baz'UPDATE Temp2 SET ID = ID + 1;ALTER TABLE Temp2 SWITCH TO Temp1;SELECT * FROM Temp2SELECT * FROM Temp1<