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 Server開發團隊在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'//獲得表識值
轉載于:https://www.cnblogs.com/aisini/archive/2010/12/24/1915615.html
總結
以上是生活随笔為你收集整理的SQL Server中如何取得刚插入的标识值的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： nginx+passenger下504
- 下一篇： RTEMS 的 AT91SAM9260