原文：Check Your SQL Server Identity來源：sqlservercentral.com作者：Andy Warren

過去的幾個月里，我有幸面試了眾多應聘DBA和DB開發崗位的求職者。我們希望開發人員能夠創建存儲過程，編寫合理的復雜SQL語句，以及觸發器。我喜歡問這些求職者一個問題：

“假設我們使用SQL Server2000進行開發。現在我需要傳遞給存儲過程兩個變量：firstname和lastname。存儲過程負責向名字為TEST的表插入這兩個變量，其中TEST表有兩個字段，名字就叫firstname和lastname。TEST表的主鍵是一個自增長類型的字段，名字叫ContactID。問題是我如何獲取插入的那條數據的主鍵值。”

讓我們想一下答案是什么。你是否知道如何創建存儲過程，獲取數值并返回給調用的應用程序？

有人會直接問我－這個問題重要嗎？對于我來說，我問這個問題的目的是為了測試求職者是否有求解非常規需求的能力。設想一下標準的訂單/訂單明細表應用場景－－你是否知道如何不知道訂單主鍵的情況下插入訂單的詳細信息？當你因為沒有使用主鍵時，可能會帶來鎖問題。這時，自增長字段加入是常用的一個手段。但是使用@@Identity來獲取插入數據的標識，有可能會帶來問題，比如在觸發器內使用就會發生問題。這并不是一個可以給出唯一答案的問題，但是這個問題可以讓我們對處理類似表的問題，來展開討論。

我收到了很多不同的回答，但是絕大多數并非最優。幾乎每個人都知道如何插入數據、如何返回值，但是幾乎每個人都在獲取自增長字段取值上回答得不是很好。

錯誤回答 #1 - Select max(contactid) from Test. 因為你無法避免別人也同時在插入數據，因此這個回答是錯誤的。當然，你可以通過提升隔離級別來達到目的，但是這將會大幅降低并發性能，因此不好。

錯誤回答 #2 - Select top 1 contactid from test order by contactid desc. 錯誤的原因和回答#1一樣。

錯誤回答 #3 - 通過插入的數據來組合成一個唯一的標識，從而獲得自增長字段的值。如果插入的數據確實組合起來是唯一的，能達到目的，但是如果不唯一，怎么辦呢？因此這也不是好辦法。

錯誤回答 #4 - 這個回答很接近正解了。這些回答者建議使用@@Identity，自然這是可以的 (小心，要知道如何正確使用@@Identity), 但是當我問他們關于@@Identity的相關技術細節時，我收到最多的答案如下：

- 對不起，我不是很清楚。- 你應該盡快獲取@@Identity的值，因為其它人的對表插入數據，也會改變這個值。- 是的，獲取最后一個identity值，在大多數情況下是可行的，但是如果在TEST表上有觸發器，這個觸發器會自動向別的表插入數據，如果那個表也同樣有一個自增長字段，那么錯誤就會發生。此時，你獲取的@@Identity取值是那個表的identity取值（注意：這個回答正確地描述了 @@Identity的行為）。 正確答案 - 因為我們使用的是SQL Server 2000，因此使用Scope_Identity() , 如果用的是SQL Server 7，那么只有只用@@Identity，并且以output參數方式傳遞(return值一般是用來作為錯誤代碼用)。使用@@Identity意味著將來也許會發生錯誤，例如審核時使用自增長字段。

現在我們來做一系列的試驗來驗證：

create database IdentityTestuse identitytestcreate table TEST (ContactID int not null identity (1, 1), firstname varchar(100) null, lastname varchar(100) null)insert into TEST Default Valuesselect @@Identity

運行后會返回1。如果在此運行，則返回 2。

insert into TEST Default Valuesselect Scope_Identity()

運行后返回 3。

現在我們來設計如何使@@Identity返回錯誤結果。我們先創建一個包含一個新的自增長字段的表TESTHISTORY，然后在TEST表上加觸發器。

create table TESTHISTORY (HistoryID int not null identity (1, 1), ContactID int not null, firstname varchar(100) null, lastname varchar(100) null)create trigger i_TEST on dbo.TEST for insert as

set nocount on

insert into TESTHISTORY (ContactID, FirstName, LastName) select ContactID, FirstName, LastName from Inserted

現在看看會發生什么：

insert into TEST Default Valuesselect @@Identity

返回值為1。注意，此時TEST表最后插入的記錄，主鍵值為4，而TESTHISTORY表作后插入記錄的主鍵值＝1。

insert into TEST Default Valuesselect @@Identity

返回值為5。TEST表最后插入記錄的主鍵值為5，而且TESTHISTORY表的確也插入了第二條記錄。現在我們再測試如果同時有別的連接來向插入TEST表插入數據，情況會如何。首先我們用當前的連接，運行：

insert into TEST Default Values

此時，TEST插入了第6條記錄。然后新建一個連接，并運行相同的SQL語句：

insert into TEST Default Values

此時，TEST表插入了第7條記錄。然后我們在原先那個連接里，來獲取“錯誤”的結果，值為3。

select @@Identity

現在我們用scope_identity()來測試。我們希望得到的結果是6，不是7！

select Scope_Identity()

結果確實如此，證明使用scope_identity()是正確的。我知道，這樣測試很麻煩，你也許不會去仔細探究。但是如果你準備使用SQL Server 2000，你就必須知道它是如何工作的。有興趣用這個問題來考考你的開發人員，可以教他們一些專業的開發技巧，這樣也許會使你在將來省卻很多本可以避免的麻煩。