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/