您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2000 取得 Identity 自增欄位值的正確姿勢：SCOPE_IDENTITY() 完勝 @@IDENTITY

編輯：關於SqlServer

面試經典題目

「存儲過程插入資料後，如何取得剛插入那筆資料的自增主鍵值？」看似簡單，卻能測試開發者對併發、觸發器副作用的理解深度。

常見錯誤作法與風險

錯誤作法	風險說明
SELECT MAX(ContactID) FROM Test	併發環境下極易取得他人插入的 ID
SELECT TOP 1 ContactID FROM Test ORDER BY ContactID DESC	同 MAX()，併發不安全
組合插入欄位形成唯一鍵反查	業務鍵不一定唯一，不可靠
使用 @@IDENTITY	若表有觸發器且觸發器插入其他含 Identity 的表，回傳值會是觸發器插入的那張表的 Identity，而非本表

正確答案：SCOPE_IDENTITY()

僅回傳當前範圍（預存程序、觸發器、批次）內最後產生的 Identity 值
不受巢狀觸發器、跨表插入影響
SQL Server 2000 以上版本皆支援
搭配 OUTPUT 參數回傳給呼叫端（RETURN 值建議保留給錯誤代碼）

完整驗證實驗腳本

-- 建立測試環境
CREATE DATABASE IdentityTest;
USE IdentityTest;
CREATE TABLE TEST (ContactID INT IDENTITY(1,1), firstname VARCHAR(100), lastname VARCHAR(100));
CREATE TABLE TESTHISTORY (HistoryID INT IDENTITY(1,1), ContactID INT, firstname VARCHAR(100), lastname VARCHAR(100));

-- 建立觸發器：插入 TEST 時同步寫入 TESTHISTORY
CREATE TRIGGER i_TEST ON dbo.TEST FOR INSERT AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TESTHISTORY (ContactID, FirstName, LastName) 
    SELECT ContactID, FirstName, LastName FROM Inserted;
END;

-- 測試 1：無觸發器干擾時兩者相同
INSERT INTO TEST DEFAULT VALUES; SELECT @@IDENTITY;   -- 1
INSERT INTO TEST DEFAULT VALUES; SELECT SCOPE_IDENTITY(); -- 3

-- 測試 2：有觸發器時 @@IDENTITY 錯誤
INSERT INTO TEST DEFAULT VALUES; SELECT @@IDENTITY;   -- 回傳 TESTHISTORY 的 HistoryID (1)
INSERT INTO TEST DEFAULT VALUES; SELECT @@IDENTITY;   -- 回傳 5 (TESTHISTORY 第二筆)

-- 測試 3：併發模擬
-- 連線 A 插入第 6 筆
-- 連線 B 插入第 7 筆
-- 連線 A 執行 SELECT @@IDENTITY → 回傳 3 (TESTHISTORY 第三筆) ❌
-- 連線 A 執行 SELECT SCOPE_IDENTITY() → 回傳 6 ✅