T-SQL筆記9_常見跟使用的系統函數(全域變數)_@@IDENTITY跟SCOPE_IDENTITY()
@@IDENTITY
傳回最後插入的識別值之系統函數，作用範圍只在該次session過程。
再生成Table Column時候我們可以設置哪個欄位要自動產生流水號
語法:
column_name type IDENTITY [ (seed , increment) ]
Identity屬性有兩個參數
seed : 就是所謂ID的初始值
increment : 則是ID值的固定遞增量
官網解釋
====================================================================
在 INSERT、SELECT INTO 或大量複製陳述式完成之後，@@IDENTITY 會包含
陳述式所產生的最後一個識別值。
如果陳述式並未影響任何含有識別欄位的資料表，@@IDENTITY 會傳回 NULL。
如果插入多個資料列，產生多個識別值，@@IDENTITY 會傳回最後一個產生的識別值。
如果陳述式引發一或多個執行插入來產生識別值的觸發程序，在陳述式之後緊接著呼叫 @@IDENTITY，會傳回觸發程序所產生的最後一個識別值。
如果在含有識別欄位之資料表的插入動作之後引發觸發程序，且觸發程序插入另一個沒有識別欄位的資料表，@@IDENTITY 會傳回第一次插入的識別值。
如果 INSERT 或 SELECT INTO 陳述式或大量複製失敗，或回復交易，
@@IDENTITY 值不會還原成先前的設定。
====================================================================
說明:
我們說一個session包含不同的scope (可能有trigger, procedure, batch , 一串動態查詢語句 , 儲存過程)
一個batch運行的多個儲存過程就會產生多個不同的scope
至於@@IDENTITY 回傳的就會是最後一個scope的結果。
若是要取得目前scope插入的最後一個ID值就需要改用SCOPE_IDENTITY()
一個示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | use testdb CREATE TABLE EmployeeData ([id] [TINYINT] IDENTITY(100, 1) PRIMARY KEY NOT NULL, [Name] [NVARCHAR](20) NULL ) ON [PRIMARY]; GO DECLARE @num INT SET @num = 1 WHILE @num <= 12 BEGIN INSERT INTO EmployeeData (Name) VALUES ('Amy' + CAST(@num as varchar(10)) ) SET @num = @num + 1 END GO select * from EmployeeData |
先產生一個員工人名表(名稱用 Amy 串接 流水號)
塞入資料筆觀察
1 2 3 4 5 6 7 | select MAX(ID) as MaxIdentity from EmployeeData --112 Insert into EmployeeData values('Amy-13') select MAX(ID) as MaxIdentity from EmployeeData --113 select @@IDENTITY --113 |
多筆塞入的觀察
若沒有任何塞入的異動發生則在全新的一個session中的
看會發現 Identity回傳為null
SCOPE_IDENTITY()
傳回插入相同範圍之識別欄位中的最後一個識別值。 範圍是一個模組：預存程序、觸發程序、函數或批次。 因此，若兩個陳述式在相同預存程序、函式或批次中，它們就在相同範圍中。
官網解釋
====================================================================
SCOPE_IDENTITY 和 @@IDENTITY 會傳回目前工作階段任何資料表中所產生的最後一個識別值。 不過，SCOPE_IDENTITY 會傳回只在目前範圍內插入的值；@@IDENTITY 不限於特定範圍。
例如，有 T1 和 T2 兩份資料表，而且 T1 定義了 INSERT 觸發程序。 當資料列插入 T1 時，會引發觸發程序，且會在 T2 中插入一個資料列。 這個狀況說明兩個範圍：在 T1 插入，以及觸發程序在 T2 插入。
假設 T1 和 T2 都有識別欄位，在 T1 的 INSERT 陳述式結束時，@@IDENTITY 和 SCOPE_IDENTITY 會傳回不同的值。 @@IDENTITY 會傳回在目前工作階段中，跨越任何範圍所插入的最後一個識別欄位值。 這是在 T2 中插入的值。 SCOPE_IDENTITY() 會傳回在 T1 中插入的 IDENTITY 值。 這是相同範圍內所發生的最後一項插入。 如果在範圍內的識別欄位執行任何 INSERT 陳述式之前叫用 SCOPE_IDENTITY() 函式，則這個函式會傳回 Null 值。
====================================================================
SCOPE示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Drop table EmployeeData CREATE TABLE EmployeeData ([id] [TINYINT] IDENTITY(1, 1) PRIMARY KEY NOT NULL, [Name] [NVARCHAR](20) NULL ) GO CREATE TABLE Departments (DepartmentID INT IDENTITY(100, 5) PRIMARY KEY, Departmentname VARCHAR(20) NULL ); Go select * from EmployeeData select * from Departments |
重新創建兩張表(員工、部門)
員工表ID從1開始每次遞增1
部門表ID從100開始每次遞增5
接著於員工表產生一Trigger
若員工表發生一次Insert就相應塞一筆IT字串內容到部門表
1 2 3 4 5 6 | CREATE TRIGGER T_INSERT_DEPARTMENT ON EmployeeData FOR INSERT AS BEGIN INSERT Departments VALUES ('IT') END; |
接著我們來塞員工表觀察看看
會發現SCOPE_IDENTITY只針對第一次塞入員工表的異動ID進行回傳
也驗證微軟官網講述的機制
使用DBCC CheckIdent 查看或修改ID值
1 | DBCC CHECKIDENT( table_name [, { NORESEED | { RESEED [, new_reseed_value ] } } ]) |
DBCC 是Database Console Commands的簡稱
透過DBCC可以幫我們查看所指定的table目前的ID值
查看目前table的ID值(不做重新編列順序種)
1 | dbcc checkident('table name',noreseed)
|
Checking identity information: current identity value '116', current column value '116'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
查看目前table的ID值(做重新編列順序種)
1 2 3 | DBCC CHECKIDENT ( 'table_name' ) --or DBCC CHECKIDENT ( 'table_name', RESEED ) |
若ID值大於目前ID值則進行取代(改為ID的Maximum
把ID修改成特定值
1 | DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )
|
Ref:
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/identity-transact-sql?view=sql-server-ver15
https://www.sqlservertutorial.net/sql-server-basics/sql-server-identity/
https://www.cnblogs.com/ljhdo/p/4798188.html
https://www.sqlshack.com/overview-of-identity-functions-in-sql/
留言
張貼留言