SQL Server 取得剛插入身分識別值的正確姿勢：@@IDENTITY vs IDENT_CURRENT vs SCOPE_IDENTITY

常見錯誤場景

表 test1 有 IDENTITY(1,1) 主鍵，插入後用 SELECT @@IDENTITY 取值。若表上有 AFTER INSERT 觸發器，觸發器內插入另一張有 IDENTITY 的表 test2，@@IDENTITY 將回傳 test2 的身分值，而非預期的 test1 值。

三大函數對比

程式碼驗證

-- 建立測試表
CREATE TABLE test1 (id INT IDENTITY(1,1), content NVARCHAR(100));
CREATE TABLE test2 (id INT IDENTITY(100,1), content NVARCHAR(100));

-- 建立觸發器：插入 test1 時同步寫入 test2
CREATE TRIGGER trg_test1_ins ON test1 AFTER INSERT
AS INSERT INTO test2 SELECT content FROM inserted;

-- 測試 1：@@IDENTITY 錯誤
INSERT INTO test1(content) VALUES('A');
SELECT @@IDENTITY;  -- 回傳 100 (test2 的值) ❌

-- 測試 2：IDENT_CURRENT 併發不安全
INSERT INTO test1(content) VALUES('B');
SELECT IDENT_CURRENT('test1');  -- 單獨執行正確，但併發時會取到其他 Session 的值 ❌

-- 測試 3：SCOPE_IDENTITY 正確且安全
INSERT INTO test1(content) VALUES('C');
SELECT SCOPE_IDENTITY();  -- 回傳 3 (test1 的值) ✅
-- 即使有觸發器、即使高併發，永遠只回傳當前 Scope 的 test1 身分值

最佳實踐總結

1. 永遠使用 SCOPE_IDENTITY() 取得剛插入的身分值。
2. 若需在觸發器內取得觸發器所在表的身分值，觸發器內同樣用 SCOPE_IDENTITY()。
3. OUTPUT 子句 (SQL Server 2005+) 可在單一陳述式中插入並回傳身分值，效能更佳、語意更清晰：

   DECLARE @NewIds TABLE (id INT);
   INSERT INTO test1(content) OUTPUT INSERTED.id INTO @NewIds VALUES('D');
   SELECT id FROM @NewIds;
   

4. 避免在高併發關鍵路徑使用 @@IDENTITY 或 IDENT_CURRENT。