SCOPE_IDENTITY (Transact-SQL)

傳回插入相同範圍之識別欄位中的最後一個識別值。範圍是一個模組:預存程序、觸發程序、函數或批次。因此,如果兩個陳述式在相同預存程序、函數或批次中,它們就在相同範圍中。

主題連結圖示Transact-SQL 語法慣例

語法

SCOPE_IDENTITY()

傳回類型

numeric(38,0)

備註

SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 是類似的函數,因為它們會傳回插入識別欄位的值。

IDENT_CURRENT 不受範圍和工作階段的限制;它只限於指定的資料表。IDENT_CURRENT 會傳回在任何工作階段和任何範圍中,產生給特定資料表的值。如需詳細資訊,請參閱<IDENT_CURRENT (Transact-SQL)>。

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 值。

失敗的陳述式和交易可能會變更資料表的目前識別,以及建立識別欄位值中的間距。識別值永遠不會回復,即使試圖將值插入資料表的交易未獲認可,也是如此。例如,如果 INSERT 陳述式因 IGNORE_DUP_KEY 違規而失敗,資料表的目前識別值仍會遞增。

範例

A. 使用 @@IDENTITY 和 SCOPE_IDENTITY 搭配觸發程序

下列範例會建立 TZ 和 TY 這兩份資料表,以及 TZ 的 INSERT 觸發程序。當資料列插入 TZ 資料表時,會引發觸發程序 (Ztrig),且會在 TY 中插入一個資料列。

USE tempdb
GO
CREATE TABLE TZ (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)

INSERT TZ
   VALUES ('Lisa')
INSERT TZ
   VALUES ('Mike')
INSERT TZ
   VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks.

Z_id Z_name

-------------

1 Lisa

2 Mike

3 Carla

CREATE TABLE TY (
   Y_id  int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

INSERT TY (Y_name)
   VALUES ('boathouse')
INSERT TY (Y_name)
   VALUES ('rocks')
INSERT TY (Y_name)
   VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:

Y_id Y_name

---------------

100 boathouse

105 rocks

110 elevator

/*Create the trigger that inserts a row in table TY 
when a row is inserted in table TZ.*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS 
   BEGIN
   INSERT TY VALUES ('')
   END

/*FIRE the trigger and determine what identity values you obtain 
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

以下為結果集:

SCOPE_IDENTITY

4

/*SCOPE_IDENTITY 傳回相同範圍中的最後一個識別值。這是在資料表 TZ 中的插入。*/

@@IDENTITY

115

/*@@IDENTITY 傳回觸發程序插入 TY 中的最後一個識別值。引發觸發程序是因為之前在 TZ 上的插入動作。*/

B. 使用 @@IDENTITY 和 SCOPE_IDENTITY() 搭配複寫

下列範例會說明如何針對為了合併式複寫而發行之資料庫的插入,使用 @@IDENTITY 和 SCOPE_IDENTITY()。範例中的兩份資料表都位於 AdventureWorks2008R2 範例資料庫中:Person.ContactType 尚未發行,而 Sales.Customer 已發行。合併式複寫會將觸發程序加入至發行的資料表。因此,@@IDENTITY 可能會根據複寫系統資料表的插入 (而非使用者資料表的插入) 傳回值。

Person.ContactType 資料表的最大識別值為 20。如果您將資料列插入此資料表,@@IDENTITY 和 SCOPE_IDENTITY() 就會傳回相同的值。

USE AdventureWorks2008R2;
GO
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO

以下為結果集:

SCOPE_IDENTITY

21

@@IDENTITY

21

Sales.Customer 資料表的最大識別值為 29483。如果您將資料列插入此資料表,@@IDENTITY 和 SCOPE_IDENTITY() 就會傳回不同的值。SCOPE_IDENTITY() 會根據使用者資料表的插入傳回值,而 @@IDENTITY 會根據複寫系統資料表的插入傳回值。您可以針對需要存取已插入識別值的應用程式,使用 SCOPE_IDENTITY()。

INSERT INTO Sales.Customer ([TerritoryID],[PersonID]) VALUES (8,NULL);
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO

以下為結果集:

SCOPE_IDENTITY

29484

@@IDENTITY

89

請參閱

參考