SQL Server中如何取得剛插入的標識值

資料庫實際應用中，我們往往需要得到剛剛插入 的標誌值來往相關表中寫入資料。但我們平常得到的真的是我們需要的那個值嗎？
有時我們會使用 SELECT @@Identity 來獲得我們剛剛插入的值，比如下面的代碼

代碼一：
use tempdb
if exists (select * from sys.objects where object_id = object_id(N'[test1]') and type in (N'u'))
drop table [test1]
go
create table test1
(
id int identity(1,1),
content nvarchar(100)
)
insert into test1 (content) values ('solorez')
select @@identity

樂觀情況下，這樣做是沒問題的，但如果我們如果先運行下面的代碼二建立一個觸發器、再運行代碼三：

代碼二：
create table test2
(
id int identity(100,1),
content nvarchar(100)
)

create trigger tri_test1_identitytest_I
on test1 after insert
as
begin
insert into test2
select content from inserted
end

代碼三：
insert into test1 (content) values ('solorez2')
select @@identity
 
我們可以看到，此時得到的標識值已經是100多了，很明顯，這是表test2的產生的標識值，已經不是我們想要的 了。
我們可以看看@@identity的定義：Identity
原來，@@identity返回的是當前事務最後插入的標識值。
這 時我們或許會用下面的方法：

代碼四：
insert into test1 (content) values ('solorez3')
SELECT IDENT_CURRENT('test1')

看來結果還比較正確，但如果我們在多次運行代碼四的同時運行下面的代碼五：

代碼五：
insert into test1 (content) values ('solorez3')

waitfor delay '00:00:20'
SELECT IDENT_CURRENT('test1')
 
結果又 不是我們想要的了！
再看看IDENT_CURRENT(Tablename) 的定義：IDENT_CURRENT(Tablename)
是 返回指定表的最後標識值。

到這裡，是該亮出答案的時候了，我們可以使用下面的代碼：

代碼六：
insert into test1 (content) values ('solorez3')
SELECT scope_identity()

這時，我們無論是添加觸發器還是運行並行插入，得到的始終是當前事務的標識值。

scope_identity()的定義：scope_identity()
 

 

Table表中有一欄位為自增長列(也稱識別欄位，即Identity Increment)，那麼如何擷取該列的下一行標識值呢，或剛插入的標識值？
 

經過一番探索，下面的代碼有效：

--擷取剛插入的標識值

SELECT IDENT_CURRENT('TableName') from TableName
 

--擷取下一行的標識值

declare @NextIdentity int

SELECT @NextIdentity = IDENT_CURRENT('TableName') + IDENT_INCR('TableName') from TableName

if (@NextIdentity is null)

   set @NextIdentity = IDENT_SEED('TableName')  --如查詢表為空白，則取識別欄位起始值

將TableName換成所需計算的表名即可。

 

摘要：本文向您介紹如何在SQL Server中準確的獲得標識值，在SQL Server中，有三種不同的函數可以協助我們達到目的。

SQL Server有三種不同的函數可以用來獲得含有識別欄位的表裡最後產生的標識值：

@@IDENTITY   SCOPE_IDENTITY()   IDENT_CURRENT('資料表名') 以上三個函數雖然都可以返回資料庫引擎最後產生插入識別欄位的值，但是根據插入行的來源(例如：預存程序或觸發器)以及插入該行的串連不同，這三個函數在功 能上也有所不同。

@@IDENTITY函數可以返回所有範圍內當前串連插入最後所產生的標識值(包括任何調用的預存程序和觸發器)。這個函數不止可以適用於表。函數 返回的值是最後表插入行產生的標識值。

SCOPE_IDENTITY()函數跟上一個函數幾乎是一摸一樣的，不同的地方：即前者返回的值只限於當前範圍(即執行中的預存程序)。

最後是IDENT_CURRENT函數，它可以用於所有範圍和所有串連，獲得最後產生的表標識值。跟前面兩個函數不同的是，這個函數只用於表，並且 使用[資料表名]作為一個參數。

我們可以舉執行個體來示範上述函數是如何運作的。

首先，我們建立兩個簡單的例表：一個代表客戶表，一個代表審計表。建立審計表的目的是為了追蹤資料庫裡插入和刪除資訊的所有記錄。

以下是引用片段：

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
然後，我們還要建立一個預存程序和一個輔助 觸發器，這個預存程序將在資料庫表裡插入新的客戶行，並返回產生的標識值，而觸發器則會向審計表插入行：

以下是引用片段：

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
現在我們可以執行程式，建立客戶表的第一行了，以下是引用片段：

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

執行後返回了我們需要的第一個客戶的值，並記錄了插入審計表的條目。到目前為止，資料顯示沒有任何問題。

假設由於先前溝通出現了偏差，一個客戶服務代表現在需要從資料庫裡刪除掉這個新增的客戶。我們現在就來把新插入的客戶行刪除掉：

以下是引用片段：

DELETE FROM dbo.customer WHERE customerid = 1 現在，客戶工作表為空白表，而審計工作表裡則有兩行——第一行是記錄第一次插入行，第二行是記錄刪除客戶記錄。

現在我們再往資料庫裡增加第二個客戶資訊並檢測一下獲得的標識值：

以下是引用片段：

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

哇!看看出現了什麼情況!如果我們現在再看客戶工作表，就會發現雖然建立了客戶2，但是我們的程式返回的標識值為3!到底出了什麼問題呢?回想一 下，前面講過@@IDENTITY函數的作用範圍，它會返回主程式調用的任何預存程序或觸動任何觸發器最後產生的標識值，取決於哪一個在函數被調用前最後 產生標識值。在我們的例子裡，初始範圍是p_InsertCustomer，然後是觸發器用來記錄插入條目的tr_customer_log。因此我們返 回獲得的標識值是審計工作表裡觸發器插入產生的標識值，而不是我們想要的客戶工作表裡的產生的標識值。

在SQL Server 2000之前的版本，@@IDENTITY函數是獲得標識值的唯一方法。由於會出現這樣的預存程序/觸發器問題，SQL ServerTeam Dev在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT這兩個函數來解決這個問題。所以在舊的SQL Server版本裡，要解決這個問題比較麻煩。如果是SQL Server6.5版本，我建議可以去掉識別欄位，然後建立一個可以包含下一個需要使用的值的輔助表，可以達到識別欄位的作用效果。不過這個辦法也不是什麼高 明的辦法。

現在我們來修改一下預存程序來使用SCOPE_IDENTITY()函數，並重新執行程式來添加第三個客戶條目：

以下是引用片段：

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，不過這次我們獲得的標識值是正確的，因為我們添加了第三個客戶條 目。如果我們檢查一下審計工作表，就會發現裡面已經有第四個條目記錄新插入的客戶記錄。由於函數SCOPE_IDENTITY()只作用於當前範圍，只返 回當前執行程式的值，這樣就避免了發生剛才那樣的問題。

前面講過，函數@@IDENTITY和函數SCOPE_IDENTITY()不止用於表，不像函數IDENT_CURRENT那樣可以用表作為參 數。使用@@IDENTITY和SCOPE_IDENTITY()這兩個函數的話在設定代碼時需要加倍小心，才能夠從所需要的表裡獲得正確的標識值。從表 面上來看，放棄這兩個函數，只使用函數IDENT_CURRENT並指定表是更安全的辦法。這樣可以避免出現獲得錯誤標識值的情況，對吧?記得先前說過函 數IDENT_CURRENT不僅會跨範圍，而且它還會跨串連。也就是說，使用這個函數產生的值不僅僅限於你的串連所執行的程式，它的涵蓋範圍還包括整個 資料庫所有的串連。因此，即使是在規模較小的OLTP環境裡，它也會出現不能準確返回所需值的問題。這樣就可能發生類似前面@@IDENTITY函數/觸 發器的資料損毀問題。

我的建議是函數SCOPE_IDENTITY()是三個函數裡最安全的函數，應該設定為預設函數。使用這個函數，你可以放心地添加觸發器和次儲存過 程，無需擔心意外損壞資料。而另外兩個函數可以保留應付特殊的情況，當遇到需要使用這兩個函數的特殊情況時，建議記錄它們的使用方式並進行測試。

小技巧：

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 顯示當前資料庫包含自增列的表
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)

擷取最後sql影響記錄的最後ID,慎用@@Identity 我們常用的獲sql影響的最後記錄id常用的是@@identity，但有些情況下會有問題，其實sql一共提供了3個不同的方法

SELECT SCOPE_IDENTITY()

select IDENT_CURRENT(TableName)

select @@IDENTITY

IDENT_CURRENT 類似於 SQL Server 2000 標識函數 SCOPE_IDENTITY 和 @@IDENTITY。這三個函數都返回最後產生的標識值。但是，上述每個函數中定義的“最後”的範圍和會話有所不同。

IDENT_CURRENT 返回為某個會話和用域中的指定表產生的最新標識值。

@@IDENTITY 返回為跨所有範圍的當前會話中的某個表產生的最新標識值。

SCOPE_IDENTITY 返回為當前會話和當前範圍中的某個表產生的最新標識值。

在空表中調用 IDENT_CURRENT 函數時，此函數將返回 NULL。

如果語句和事務失敗，它們會更改表的當前標識，從而使識別欄位中的值出現不連貫現象。即使未提交試圖向表中插入值的事務，也永遠無法復原標識值。例 如，如果因 IGNORE_DUP_KEY 衝突而導致 INSERT 語句失敗，表的當前標識值仍然會增加。

@@IDENTITY 表示插入後新的表示種子值，例子如下：
假如已經存在表job，向表插入一條資料，
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'//獲得表識值