原文：Check Your SQL Server Identity來(lái)源：sqlservercentral.com作者：Andy Warren

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

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

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

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

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

錯(cuò)誤回答 #1 - Select max(contactid) from Test. 因?yàn)槟銦o(wú)法避免別人也同時(shí)在插入數(shù)據(jù)，因此這個(gè)回答是錯(cuò)誤的。當(dāng)然，你可以通過(guò)提升隔離級(jí)別來(lái)達(dá)到目的，但是這將會(huì)大幅降低并發(fā)性能，因此不好。

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

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

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

- 對(duì)不起，我不是很清楚。- 你應(yīng)該盡快獲取@@Identity的值，因?yàn)槠渌说膶?duì)表插入數(shù)據(jù)，也會(huì)改變這個(gè)值。- 是的，獲取最后一個(gè)identity值，在大多數(shù)情況下是可行的，但是如果在TEST表上有觸發(fā)器，這個(gè)觸發(fā)器會(huì)自動(dòng)向別的表插入數(shù)據(jù)，如果那個(gè)表也同樣有一個(gè)自增長(zhǎng)字段，那么錯(cuò)誤就會(huì)發(fā)生。此時(shí)，你獲取的@@Identity取值是那個(gè)表的identity取值（注意：這個(gè)回答正確地描述了 @@Identity的行為）。 正確答案 - 因?yàn)槲覀兪褂玫氖荢QL Server 2000，因此使用Scope_Identity() , 如果用的是SQL Server 7，那么只有只用@@Identity，并且以output參數(shù)方式傳遞(return值一般是用來(lái)作為錯(cuò)誤代碼用)。使用@@Identity意味著將來(lái)也許會(huì)發(fā)生錯(cuò)誤，例如審核時(shí)使用自增長(zhǎng)字段。

現(xiàn)在我們來(lái)做一系列的試驗(yàn)來(lái)驗(yà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)行后會(huì)返回1。如果在此運(yùn)行，則返回 2。

insert into TEST Default Valuesselect Scope_Identity()

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

現(xiàn)在我們來(lái)設(shè)計(jì)如何使@@Identity返回錯(cuò)誤結(jié)果。我們先創(chuàng)建一個(gè)包含一個(gè)新的自增長(zhǎ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)在看看會(huì)發(fā)生什么：

insert into TEST Default Valuesselect @@Identity

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

insert into TEST Default Valuesselect @@Identity

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

insert into TEST Default Values

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

insert into TEST Default Values

此時(shí)，TEST表插入了第7條記錄。然后我們?cè)谠饶莻€(gè)連接里，來(lái)獲取“錯(cuò)誤”的結(jié)果，值為3。

select @@Identity

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

select Scope_Identity()

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