從SQL Server 2012開始有了Sequence,簡單用列如下:
CREATE SEQUENCE TestSeq START WITH 1 INCREMENT BY 1 ; SELECT NEXT VALUE FOR TestSeq AS NextValue;
在這之前,表中生成序列號大多都是藉助IDENTITY列屬性,當然也有一些時候,是在自定義表中,自己維護序列號。
一. 建立IDENTITY列
if OBJECT_ID('test','U') is not null drop table test GO create table test(id int identity, c1 char(1)) insert test values('a'); insert test values('b'); select * from test
1. 沒有指定IDENTITY(seed ,increment),預設就是 IDENTITY(1, 1),效果同如下語句
create table test(id int identity(1,1), c1 char(1))
2. 通過函式或者系統檢視,都可以檢視是否為IDENTITY列
SELECT COLUMNPROPERTY(OBJECT_ID('test'),'id','IsIdentity') AS is_identity select object_name(object_id) as table_name, is_identity,* from sys.columns where object_id=object_id('test') --and is_identity=1
3. 重置IDENTITY列的初始值,通常在資料刪除/歸檔後進行
DELETE test DBCC CHECKIDENT('test', RESEED, 1) DBCC CHECKIDENT('test', NORESEED) --TRUNCATE表後會自動重置IDENTITY列 TRUNCATE TABLE test DBCC CHECKIDENT('test', NORESEED)
二. 獲取IDENTITY列值
插入了資料,有時還需要獲取剛才生成的序列值另作他用,返回給前端也好,或者插入其他將來需要關聯的表。
記得曾經有個面試題:假設當前表IDENTITY列最大值為N,在儲存過程中,對這個表插入1行資料,獲取到的IDENTITY列值有時小於或者大於N+1,可能是什麼原因?
獲取IDENTITY列值有三種方式:
(1) IDENT_CURRENT( 'table_name' ) 返回為任何會話和任何作用域中的特定表最後生成的標識值。
(2) @@IDENTITY 返回為當前會話的所有作用域中的任何表最後生成的標識值。
(3) SCOPE_IDENTITY() 返回為當前會話和當前作用域中的任何表最後生成的標識值。
IDENT_CURRENT( 'table_name' ) 針對特定表,是全域性的。@@IDENTITY和SCOPE_IDENTITY()針對所有表,區別在於作用域,也就是上下文:
(1) 如果當前INSERT語句上有函式,觸發器等(不同作用域的)物件返回的IDENTITY值,那麼@@IDENTITY會取所有表上的最後1個,而不是當前表上的;
(2) SCOPE_IDENTITY()會取當前作用域所有表上最後1個IDENTITY值,被呼叫的函式,觸發器已經超出了作用域/上下文。所以在使用INSERT後,接著使用SCOPE_IDENTITY()獲取IDENTITY列值,就不會有問題了:
insert test values('z'); select SCOPE_IDENTITY() as curr_value
一個GO語句/批處理,也是一個上下文的分界點,但是SQL語句是順序執行的,所以一個會話裡,只要在INSERT之後用SCOPE_IDENTITY()來獲取IDENTITY值是沒問題的。
三. 修改IDENTITY列值/屬性
1. 對已存在的列增加/刪除IDENTITY屬性
if OBJECT_ID('t_id') is not null drop table t_id GO create table t_id(id int,c1 char(1)) insert into t_id select 1,'a' union all select 2,'b' alter table t_id alter column id int identity(1,2) /* Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'identity'. */
直接修改列屬性會報錯,IDENTITY屬性只能伴隨著列增加/刪除。
(1) 利用中間表
在SSMS介面上設計表(SSMS/Tables/Design),可以直接增加/刪除列上的IDENTITY屬性,如果生成指令碼看看的話(右擊編輯框/工具欄/選單欄),可以發現SSMS是利用了中間表,並非在原表直接修改屬性。
表上有約束,索引等物件時,指令碼會更加繁雜些。示例如下圖:
如果出現如下錯誤:
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.
是因為SSMS裡有個選項沒設定,SQL Server認為有刪除/重建表的指令碼不安全,所以預設關閉了,需要手動開啟一下,去掉那個勾:
對錶上已存在列新增IDENTITY屬性,生成的指令碼如下:
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_t_id ( id int NOT NULL IDENTITY (1, 1), c1 char(1) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_t_id SET (LOCK_ESCALATION = TABLE) GO SET IDENTITY_INSERT dbo.Tmp_t_id ON GO IF 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)') GO SET IDENTITY_INSERT dbo.Tmp_t_id OFF GO DROP TABLE dbo.t_id GO EXECUTE sp_rename N'dbo.Tmp_t_id', N't_id', 'OBJECT' GO COMMIT
對錶上已存在列刪除IDENTITY屬性,生成的指令碼如下:
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_t_id ( id int NOT NULL, c1 char(1) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_t_id SET (LOCK_ESCALATION = TABLE) GO IF 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)') GO DROP TABLE dbo.t_id GO EXECUTE sp_rename N'dbo.Tmp_t_id', N't_id', 'OBJECT' GO COMMIT
(2) 利用中間列
對錶上已存在列刪除IDENTITY屬性
if OBJECT_ID('t_id') is not null drop table t_id GO create table t_id(id int identity(1,1),c1 char(1)) insert into t_id select 'a' union all select 'b' select * from t_id SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity') --在表上新增一個列,把IDENTITY列值複製過去 alter table t_id add id_new int GO update t_id set id_new = id --刪除原來的列,並重新命名新增列 alter table t_id drop column id exec sp_rename 't_id.id_new','id' select * from t_id SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')
對錶上已存在列新增IDENTITY屬性,用中間列的方式不太可行,因為IDENTITY列不接受UPDATE,新增的IDENTITY列無法直接複製原id的值,還得藉助中間表,但如果不需要原來id的值,那麼可以:
if OBJECT_ID('t_id') is not null drop table t_id GO create table t_id(id int,c1 char(1)) insert into t_id select 1,'a' union all select 3,'b' select * from t_id SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity') --在表上新增一個IDENTITY列,不復制原來的ID值 alter table t_id add id_new int identity(1,1) not null --刪除原來的列,並重新命名新增列 alter table t_id drop column id exec sp_rename 't_id.id_new','id' select * from t_id SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')
2. 在IDENTITY列上做增刪改操作(DML)
(1) 刪除操作沒有問題,直接DELETE即可
delete test where id = 2
(2) 如果要顯式INSERT某個值,需要開啟IDENTITY_INSERT這個SESSION級的選項
set IDENTITY_INSERT test on; insert test(id,c1) values(3,'c'); set IDENTITY_INSERT test off; select * from test
(3) 如果要UPDATE IDENTITY列值,無論是否開啟IDENTITY_INSERT這個選項都無法更新
set IDENTITY_INSERT test on; update test set id = 10 where id = 1 set IDENTITY_INSERT test off; /* Msg 8102, Level 16, State 1, Line 1 Cannot update identity column 'id'. */
非要修改的話,就得藉助中間表,在不含IDENTITY屬性的中間表裡做完UPDATE,然後再把資料導回來。中間表可參考上面的指令碼。
3. IDENTITY列屬性複製
(1) 直接從單表SELECT INTO table_name,原表其他約束,索引等等都不會被複制,但是IDENTITY屬性會被複制。
select * into test2 from test select * from test2 select columnproperty(OBJECT_ID('test'),'id','IsIdentity') select columnproperty(OBJECT_ID('test2'),'id','IsIdentity')
(2) 如果有IDENTITY屬性的表和其他表JOIN,那麼IDENTITY屬性不會被複制。
select a.* into test3 from test a inner join sys.objects b on a.id = b.object_id select * from test3 select columnproperty(OBJECT_ID('test3'),'id','IsIdentity')
假如複製表時,不想要IDENTITY屬性,正好可以利用一下這個特點,如下:
select a.* into test4 from test a inner join sys.objects b on 1=2
(3) 如果用SELECT INTO table_name導資料時,FROM子句有多表關聯,且想要保留IDENTITY屬性,這時可以用INSERT,並考慮使用TABLOCK提示
if OBJECT_ID('test5','U') is not null drop table test5 GO create table test5(id int identity, c1 char(1)) select * from test5 GO set IDENTITY_INSERT test5 on; insert into test5 WITH(TABLOCK) (id,c1) select a.* from test a inner join test2 b on a.id = b.id set IDENTITY_INSERT test5 off; select * from test5 select columnproperty(OBJECT_ID('test5'),'id','IsIdentity')
這裡使用了WITH(TABLOCK)選項,在SIMPLE或者BULK_LOGGED恢復模式下,SELECT…INTO table_name和INSERT INTO table_name WITH(TABLOCK)都能最小化日誌。
4. 藉助SWITCH來處理IDENTITY屬性,推薦
同樣也是利用中間表,上面的幾個列子都使用了INSERT,這裡使用SWITCH,不再有資料倒來倒去的開銷,需要SQL Server 2008及以上版本,能比較有效地同時解決上面的3個問題:
(1) 不能直接對錶上現有列增加/刪除IDENTITY屬性;
(2) 不能直接更新IDENTITY列;
(3) 複製表時,有選擇的複製IDENTITY列屬性(多表關聯,對關聯後的表做SWITCH以實現);
CREATE TABLE Temp1 ( ID INT IDENTITY(1,1) PRIMARY KEY, X VARCHAR(10) ) INSERT INTO Temp1 OUTPUT INSERTED.* SELECT 'Foo' UNION ALL SELECT 'Bar' UNION ALL SELECT '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 Temp2 OUTPUT INSERTED.* SELECT 10,'Foo' UNION ALL SELECT 20,'Bar' UNION ALL SELECT 5, 'Baz' UPDATE Temp2 SET ID = ID + 1; ALTER TABLE Temp2 SWITCH TO Temp1; SELECT * FROM Temp2 SELECT * FROM Temp1
另外,從SQL Server 2012開始,如果開發時使用了SEQUENCE,這些IDENTITY列的限制就都不會存在了。
四. IDENTITY函式
這是一個函式,使用時和IDENTITY屬性的格式很相似,不過兩者沒什麼關係,純粹因為名字相同,順便提一下。
select IDENTITY(int,1,1) as id into #t from sysobjects select cast(IDENTITY(int,1,1) as varchar(1000)) as id into #t2 from sysobjects -- can not use expression with identity function directly
IDENTITY函式限制比較多,只能用在SELECT INTO語句裡,不能結合表示式使用,而且有了ROW_NUMBER(),IDENTITY函式就更顯得不好用了。