SQL SCOPE_IDENTITY() 與 @@IDENTITY 取得自動增量值差異

前端畫面在新增資料時，很常需要對此資料做後續的處理，需要此次新增資料的IDENTITY。或是後端資料庫若有資料變動時常會有觸發器進而更改或是新增資料，也需要當下新增的資料的IDENTITY做後續處理的需求。此篇記錄一下SQL Server使用 SCOPE_IDENTITY() 與 @@IDENTITY 的實際差異。

SCOPE_IDENTITY() 函數是返回當前作用域中最近插入的自動增量值。也就是說，它只會返回當前執行 INSERT 陳述式時所產生的自動增量值，並不會受到任何觸發器所產生的自動增量值的影響。

@@IDENTITY 函數是返回最後一個 INSERT 陳述式所產生的自動增量值，而不管是在哪個作用域內產生的。所以如果INSERT 陳述式中包含了觸發器，則 @@IDENTITY 函數可能會返回不是預期的自動增量值。

講起來有點複雜，直接實作範例。

1.建立範例資料表

SQL
CREATE TABLE ExampleTable (
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name NVARCHAR(50) NOT NULL
);

2.建立觸發器，若有資料新增，則再複製一筆相同資料

SQL
CREATE TRIGGER ExampleTrigger
ON ExampleTable
FOR INSERT
AS
BEGIN
  INSERT INTO ExampleTable (Name)
  SELECT Name FROM INSERTED
END;

3.新增 Name 為 Alvin 的資料，同時會啟動觸發器，資料表會有兩筆Alvin。

SQL
INSERT INTO ExampleTable (Name)
VALUES ('Alvin');

SELECT SCOPE_IDENTITY()
SELECT @@IDENTITY

DROP TABLE ExampleTable;

資料表內容如下

SELECT SCOPE_IDENTITY() 結果為 1
SELECT @@IDENTITY 結果為 2

可以很明顯看出，SCOPE_IDENTITY()就是取得當下 insert 的ID，而 @@IDENTITY 則是會取得觸發器新增資料的ID。

在取得自動增量值時，如果是要對此次新增的資料做後續處理，請使用 SCOPE_IDENTITY()，使用 @@IDENTITY 有可能會取到因為此次新增而額外新增的值。

SQL

Alvin

軟體工程師，喜歡金融知識、健康觀念、心理哲學、自助旅遊與系統設計。

紀錄後端如何實現 Lazy loading 查詢無限層結構的資料。

紀錄藉由索引優化查詢性能技巧方式，叢集與非叢集索引差異。

當系統資料量日益龐大常會遇到資料庫查詢時間過久的情況，此篇提供資料庫查詢性能優化的幾個方法。

SQL-partition by 實現資料分組

使用SQL partition by 實現資料分割，比較LINQ與partition by效能

SQL-merge into 語法紀錄

這次被指派實作不同資料庫同步的功能，公司現有一個ERP系統，資料來源一律以此為標準，讓客戶可在自家系統新增、編輯、寫入ERP、與ERP同步...

介紹如何在 Next.js 專案中使用 Fuse.js 實作站內搜尋功能，包含預先建立 Json 檔以及根據使用者輸入查詢關鍵字動態呈現對應內容。

紀錄後端如何實現 Lazy loading 查詢無限層結構的資料。

紀錄藉由索引優化查詢性能技巧方式，叢集與非叢集索引差異。

當系統資料量日益龐大常會遇到資料庫查詢時間過久的情況，此篇提供資料庫查詢性能優化的幾個方法。

SQL-partition by 實現資料分組

使用SQL partition by 實現資料分割，比較LINQ與partition by效能

SQL-merge into 語法紀錄

這次被指派實作不同資料庫同步的功能，公司現有一個ERP系統，資料來源一律以此為標準，讓客戶可在自家系統新增、編輯、寫入ERP、與ERP同步...

介紹如何在 Next.js 專案中使用 Fuse.js 實作站內搜尋功能，包含預先建立 Json 檔以及根據使用者輸入查詢關鍵字動態呈現對應內容。