SQL Server有三種不同的函數(shù)可以用來獲得含有標識列的表里最后生成的標識值：

@@IDENTITY   
SCOPE_IDENTITY()   
IDENT_CURRENT('數(shù)據(jù)表名') 

以上三個函數(shù)雖然都可以返回數(shù)據(jù)庫引擎最后生成插入標識列的值，但是根據(jù)插入行的來源(例如：存儲過程或觸發(fā)器)以及插入該行的連接不同，這三個函數(shù)在功能上也有所不同。

@@IDENTITY函數(shù)可以返回所有范圍內(nèi)當前連接插入最后所生成的標識值(包括任何調(diào)用的存儲過程和觸發(fā)器)。這個函數(shù)不止可以適用于表。函數(shù)返回的值是最后表插入行生成的標識值。

SCOPE_IDENTITY()函數(shù)跟上一個函數(shù)幾乎是一摸一樣的，不同的地方：即前者返回的值只限于當前范圍(即執(zhí)行中的存儲過程)。

最后是IDENT_CURRENT函數(shù)，它可以用于所有范圍和所有連接，獲得最后生成的表標識值。跟前面兩個函數(shù)不同的是，這個函數(shù)只用于表，并且使用[數(shù)據(jù)表名]作為一個參數(shù)。

我們可以舉實例來演示上述函數(shù)是如何運作的。

首先，我們創(chuàng)建兩個簡單的例表：一個代表客戶表，一個代表審計表。創(chuàng)建審計表的目的是為了跟蹤數(shù)據(jù)庫里插入和刪除信息的所有記錄。

以下是引用片段：

CREATE TABLE dbo.customer   
(customerid INT IDENTITY(1,1) PRIMARY KEY)   
GO   
CREATE TABLE dbo.auditlog   
(auditlogid INT IDENTITY(1,1) PRIMARY KEY,   
customerid INT, action CHAR(1),   
changedate datetime DEFAULT GETDATE())   
GO 

然后，我們還要創(chuàng)建一個存儲過程和一個輔助觸發(fā)器，這個存儲過程將在數(shù)據(jù)庫表里插入新的客戶行，并返回生成的標識值，而觸發(fā)器則會向?qū)徲嫳聿迦胄校?/P>

以下是引用片段：

CREATE PROCEDURE dbo.p_InsertCustomer @customerid INT output   
AS   
SET nocount ON   
INSERT INTO dbo.customer DEFAULT VALUES   
SELECT @customerid = @@identity   
GO   
CREATE TRIGGER dbo.tr_customer_log ON dbo.customer   
FOR INSERT, DELETE   
AS   
IF EXISTS (SELECT 'x' FROM inserted)   
INSERT INTO dbo.auditlog (customerid, action)   
SELECT customerid, 'I'   
FROM inserted   
ELSE   
IF EXISTS (SELECT 'x' FROM deleted)   
INSERT INTO dbo.auditlog (customerid, action)   
SELECT customerid, 'D'   
FROM deleted   
GO 

現(xiàn)在我們可以執(zhí)行程序，創(chuàng)建客戶表的第一行了，以下是引用片段：

DECLARE @customerid INT   
EXEC dbo.p_InsertCustomer @customerid output   
SELECT @customerid AS customerid 

執(zhí)行后返回了我們需要的第一個客戶的值，并記錄了插入審計表的條目。到目前為止，數(shù)據(jù)顯示沒有任何問題。

假設由于先前溝通出現(xiàn)了偏差，一個客戶服務代表現(xiàn)在需要從數(shù)據(jù)庫里刪除掉這個新增的客戶。我們現(xiàn)在就來把新插入的客戶行刪除掉：

以下是引用片段：

DELETE FROM dbo.customer WHERE customerid = 1 

現(xiàn)在，客戶工作表為空表，而審計工作表里則有兩行——第一行是記錄第一次插入行，第二行是記錄刪除客戶記錄。

現(xiàn)在我們再往數(shù)據(jù)庫里增加第二個客戶信息并檢測一下獲得的標識值：

以下是引用片段：

DECLARE @customerid INT   
EXEC dbo.p_InsertCustomer @customerid output   
SELECT @customerid AS customerid 

哇!看看出現(xiàn)了什么情況!如果我們現(xiàn)在再看客戶工作表，就會發(fā)現(xiàn)雖然創(chuàng)建了客戶2，但是我們的程序返回的標識值為3!到底出了什么問題呢?回想一下，前面講過@@IDENTITY函數(shù)的作用范圍，它會返回主程序調(diào)用的任何存儲過程或觸動任何觸發(fā)器最后生成的標識值，取決于哪一個在函數(shù)被調(diào)用前最后生成標識值。在我們的例子里，初始范圍是p_InsertCustomer，然后是觸發(fā)器用來記錄插入條目的tr_customer_log。因此我們返回獲得的標識值是審計工作表里觸發(fā)器插入生成的標識值，而不是我們想要的客戶工作表里的生成的標識值。

在SQL Server 2000之前的版本，@@IDENTITY函數(shù)是獲得標識值的唯一方法。由于會出現(xiàn)這樣的存儲過程/觸發(fā)器問題，SQL Server開發(fā)團隊在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT這兩個函數(shù)來解決這個問題。所以在舊的SQL Server版本里，要解決這個問題比較麻煩。如果是SQL Server6.5版本，我建議可以去掉標識列，然后創(chuàng)建一個可以包含下一個需要使用的值的輔助表，可以達到標識列的作用效果。不過這個辦法也不是什么高明的辦法。

現(xiàn)在我們來修改一下存儲過程來使用SCOPE_IDENTITY()函數(shù)，并重新執(zhí)行程序來添加第三個客戶條目：

以下是引用片段：

ALTER PROCEDURE dbo.p_InsertCustomer @customerid INT output   
AS   
SET nocount ON   
INSERT INTO dbo.customer DEFAULT VALUES   
SELECT @customerid = SCOPE_IDENTITY()   
GO   
DECLARE @customerid INT   
EXEC dbo.p_InsertCustomer @customerid output   
SELECT @customerid AS customerid 

我們返回的標識值還是3，不過這次我們獲得的標識值是正確的，因為我們添加了第三個客戶條目。如果我們檢查一下審計工作表，就會發(fā)現(xiàn)里面已經(jīng)有第四個條目記錄新插入的客戶記錄。由于函數(shù)SCOPE_IDENTITY()只作用于當前范圍，只返回當前執(zhí)行程序的值，這樣就避免了發(fā)生剛才那樣的問題。

前面講過，函數(shù)@@IDENTITY和函數(shù)SCOPE_IDENTITY()不止用于表，不像函數(shù)IDENT_CURRENT那樣可以用表作為參數(shù)。使用@@IDENTITY和SCOPE_IDENTITY()這兩個函數(shù)的話在設置代碼時需要加倍小心，才能夠從所需要的表里獲得正確的標識值。從表面上來看，放棄這兩個函數(shù)，只使用函數(shù)IDENT_CURRENT并指定表是更安全的辦法。這樣可以避免出現(xiàn)獲得錯誤標識值的情況，對吧?記得先前說過函數(shù)IDENT_CURRENT不僅會跨范圍，而且它還會跨連接。也就是說，使用這個函數(shù)生成的值不僅僅限于你的連接所執(zhí)行的程序，它的涵蓋范圍還包括整個數(shù)據(jù)庫所有的連接。因此，即使是在規(guī)模較小的OLTP環(huán)境里，它也會出現(xiàn)不能準確返回所需值的問題。這樣就可能發(fā)生類似前面@@IDENTITY函數(shù)/觸發(fā)器的數(shù)據(jù)損壞問題。

我的建議是函數(shù)SCOPE_IDENTITY()是三個函數(shù)里最安全的函數(shù)，應該設置為默認函數(shù)。使用這個函數(shù)，你可以放心地添加觸發(fā)器和次存儲過程，無需擔心意外損壞數(shù)據(jù)。而另外兩個函數(shù)可以保留應付特殊的情況，當遇到需要使用這兩個函數(shù)的特殊情況時，建議記錄它們的使用情況并進行測試。

小技巧：

Sql Server 判斷表是存在標識列

If Exists(Select * from SysColumns Where ID=OBJECT_ID(N'TEST1') And COLUMNPROPERTY(ID,Name,'IsIdentity')=1)

Print N'有自增列'

Else

Print N'沒有自增列'

Sql Server 顯示當前數(shù)據(jù)庫包含自增列的表

Select b.name,a.* from SysColumns a,sysobjects b Where a.id=b.id and COLUMNPROPERTY(a.ID,a.Name,'IsIdentity')=1

SQL SERVER自增張字段復位方法：

SQLSERVER 復位:

Truncate table Ashare_CJHB

Dbcc checkident (Ashare_CJHB,RESEED,0)

【編輯推薦】

1. 在T-SQL中使用臨時表的注意事項(1)
2. SQL Server數(shù)據(jù)庫管理常用的SQL和T-SQL語句(1)
3. 用T-SQL操作面試SQL Server開發(fā)人員(1)
4. 用代碼刷新ODBC鏈接SQL Server表
5. T-SQL實用例句