過去的幾個月裡，我有幸面試了眾多應聘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 IdentityTest
use identitytest
create table TEST (ContactID int not null identity (1, 1), firstname varchar(100) null, lastname varchar(100) null)
insert into TEST Default Values
select @@Identity
運行後會返回1。如果在此運行，則返回 2。
insert into TEST Default Values
select 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 Values
select @@Identity
返回值為1。注意，此時TEST表最後插入的記錄，主鍵值為4，而TESTHISTORY表作後插入記錄的主鍵值＝1。
insert into TEST Default Values
select @@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，你就必須知道它是如何工作的。有興趣用這個問題來考考你的開發人員，可以教他們一些專業的開發技巧，這樣也許會使你在將來省卻很多本可以避免的麻煩。
