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

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

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

讓我們想一下答案是什么。你是否知道如何創(chuàng)建存儲過程，獲取數(shù)值并返回給調(diào)用的應(yīng)用程序？

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

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

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

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

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

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

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

現(xiàn)在我們來做一系列的試驗來驗證：

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

運(yùn)行后會返回1。如果在此運(yùn)行，則返回 2。

insert into TEST Default Valuesselect Scope_Identity()

運(yùn)行后返回 3。

現(xiàn)在我們來設(shè)計如何使@@Identity返回錯誤結(jié)果。我們先創(chuàng)建一個包含一個新的自增長字段的表TESTHISTORY，然后在TEST表上加觸發(fā)器。

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

現(xiàn)在看看會發(fā)生什么：

insert into TEST Default Valuesselect @@Identity

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

insert into TEST Default Valuesselect @@Identity

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

insert into TEST Default Values

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

insert into TEST Default Values

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

select @@Identity

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

select Scope_Identity()

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