數(shù)據(jù)庫實(shí)際應(yīng)用中，我們往往需要得到剛剛插入 的標(biāo)志值來往相關(guān)表中寫入數(shù)據(jù)。但我們平常得到的真的是我們需要的那個(gè)值么？
有時(shí)我們會(huì)使用 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
樂觀情況下，這樣做是沒問題的，但如果我們?nèi)绻冗\(yùn)行下面的代碼二創(chuàng)建一個(gè)觸發(fā)器、再運(yùn)行代碼三：
代碼二：
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
我們可以看到，此時(shí)得到的標(biāo)識(shí)值已經(jīng)是100多了，很明顯，這是表test2的生成的標(biāo)識(shí)值，已經(jīng)不是我們想要的 了。
我們可以看看@@identity的定義：Identity
原來，@@identity返回的是當(dāng)前事務(wù)最后插入的標(biāo)識(shí)值。
這 時(shí)我們或許會(huì)用下面的方法：
代碼四：
insert into test1 (content) values ('solorez3')
SELECT IDENT_CURRENT('test1')
看來結(jié)果還比較正確，但如果我們?cè)诙啻芜\(yùn)行代碼四的同時(shí)運(yùn)行下面的代碼五：
代碼五：
insert into test1 (content) values ('solorez3')
waitfor delay '00:00:20'
SELECT IDENT_CURRENT('test1')
結(jié)果又 不是我們想要的了！
再看看IDENT_CURRENT(Tablename) 的定義：IDENT_CURRENT(Tablename)
是 返回指定表的最后標(biāo)識(shí)值。
到這里，是該亮出答案的時(shí)候了，我們可以使用下面的代碼：
代碼六：
insert into test1 (content) values ('solorez3')
SELECT scope_identity()
這時(shí)，我們無論是添加觸發(fā)器還是運(yùn)行并行插入，得到的始終是當(dāng)前事務(wù)的標(biāo)識(shí)值。
scope_identity()的定義：scope_identity()
Table表中有一字段為自增長(zhǎng)列(也稱標(biāo)識(shí)列，即Identity Increment)，那么如何獲取該列的下一行標(biāo)識(shí)值呢，或剛插入的標(biāo)識(shí)值？
經(jīng)過一番探索，下面的代碼有效：
--獲取剛插入的標(biāo)識(shí)值
SELECT IDENT_CURRENT('TableName') from TableName
--獲取下一行的標(biāo)識(shí)值
declare @NextIdentity int
SELECT @NextIdentity = IDENT_CURRENT('TableName') + IDENT_INCR('TableName') from TableName
if (@NextIdentity is null)
set @NextIdentity = IDENT_SEED('TableName') --如查詢表為空，則取標(biāo)識(shí)列起始值
將TableName換成所需計(jì)算的表名即可。
摘要：本文向您介紹如何在SQL Server中準(zhǔn)確的獲得標(biāo)識(shí)值，在SQL Server中，有三種不同的函數(shù)可以幫助我們達(dá)到目的。
SQL Server有三種不同的函數(shù)可以用來獲得含有標(biāo)識(shí)列的表里最后生成的標(biāo)識(shí)值：
@@IDENTITY SCOPE_IDENTITY() IDENT_CURRENT('數(shù)據(jù)表名') 以上三個(gè)函數(shù)雖然都可以返回?cái)?shù)據(jù)庫引擎最后生成插入標(biāo)識(shí)列的值，但是根據(jù)插入行的來源(例如：存儲(chǔ)過程或觸發(fā)器)以及插入該行的連接不同，這三個(gè)函數(shù)在功 能上也有所不同。
@@IDENTITY函數(shù)可以返回所有范圍內(nèi)當(dāng)前連接插入最后所生成的標(biāo)識(shí)值(包括任何調(diào)用的存儲(chǔ)過程和觸發(fā)器)。這個(gè)函數(shù)不止可以適用于表。函數(shù) 返回的值是最后表插入行生成的標(biāo)識(shí)值。
SCOPE_IDENTITY()函數(shù)跟上一個(gè)函數(shù)幾乎是一摸一樣的，不同的地方：即前者返回的值只限于當(dāng)前范圍(即執(zhí)行中的存儲(chǔ)過程)。
最后是IDENT_CURRENT函數(shù)，它可以用于所有范圍和所有連接，獲得最后生成的表標(biāo)識(shí)值。跟前面兩個(gè)函數(shù)不同的是，這個(gè)函數(shù)只用于表，并且 使用[數(shù)據(jù)表名]作為一個(gè)參數(shù)。
我們可以舉實(shí)例來演示上述函數(shù)是如何運(yùn)作的。
首先，我們創(chuàng)建兩個(gè)簡(jiǎn)單的例表：一個(gè)代表客戶表，一個(gè)代表審計(jì)表。創(chuàng)建審計(jì)表的目的是為了跟蹤數(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
然后，我們還要?jiǎng)?chuàng)建一個(gè)存儲(chǔ)過程和一個(gè)輔助 觸發(fā)器，這個(gè)存儲(chǔ)過程將在數(shù)據(jù)庫表里插入新的客戶行，并返回生成的標(biāo)識(shí)值，而觸發(fā)器則會(huì)向?qū)徲?jì)表插入行：
以下是引用片段：
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í)行后返回了我們需要的第一個(gè)客戶的值，并記錄了插入審計(jì)表的條目。到目前為止，數(shù)據(jù)顯示沒有任何問題。
假設(shè)由于先前溝通出現(xiàn)了偏差，一個(gè)客戶服務(wù)代表現(xiàn)在需要從數(shù)據(jù)庫里刪除掉這個(gè)新增的客戶。我們現(xiàn)在就來把新插入的客戶行刪除掉：
以下是引用片段：
DELETE FROM dbo.customer WHERE customerid = 1 現(xiàn)在，客戶工作表為空表，而審計(jì)工作表里則有兩行——第一行是記錄第一次插入行，第二行是記錄刪除客戶記錄。
現(xiàn)在我們?cè)偻鶖?shù)據(jù)庫里增加第二個(gè)客戶信息并檢測(cè)一下獲得的標(biāo)識(shí)值：
以下是引用片段：
DECLARE @customerid INT EXEC dbo.p_InsertCustomer @customerid output SELECT @customerid AS customerid
哇!看看出現(xiàn)了什么情況!如果我們現(xiàn)在再看客戶工作表，就會(huì)發(fā)現(xiàn)雖然創(chuàng)建了客戶2，但是我們的程序返回的標(biāo)識(shí)值為3!到底出了什么問題呢?回想一 下，前面講過@@IDENTITY函數(shù)的作用范圍，它會(huì)返回主程序調(diào)用的任何存儲(chǔ)過程或觸動(dòng)任何觸發(fā)器最后生成的標(biāo)識(shí)值，取決于哪一個(gè)在函數(shù)被調(diào)用前最后 生成標(biāo)識(shí)值。在我們的例子里，初始范圍是p_InsertCustomer，然后是觸發(fā)器用來記錄插入條目的tr_customer_log。因此我們返 回獲得的標(biāo)識(shí)值是審計(jì)工作表里觸發(fā)器插入生成的標(biāo)識(shí)值，而不是我們想要的客戶工作表里的生成的標(biāo)識(shí)值。
在SQL Server 2000之前的版本，@@IDENTITY函數(shù)是獲得標(biāo)識(shí)值的唯一方法。由于會(huì)出現(xiàn)這樣的存儲(chǔ)過程/觸發(fā)器問題，SQL Server開發(fā)團(tuán)隊(duì)在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT這兩個(gè)函數(shù)來解決這個(gè)問題。所以在舊的SQL Server版本里，要解決這個(gè)問題比較麻煩。如果是SQL Server6.5版本，我建議可以去掉標(biāo)識(shí)列，然后創(chuàng)建一個(gè)可以包含下一個(gè)需要使用的值的輔助表，可以達(dá)到標(biāo)識(shí)列的作用效果。不過這個(gè)辦法也不是什么高 明的辦法。
現(xiàn)在我們來修改一下存儲(chǔ)過程來使用SCOPE_IDENTITY()函數(shù)，并重新執(zhí)行程序來添加第三個(gè)客戶條目：
以下是引用片段：
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
我們返回的標(biāo)識(shí)值還是3，不過這次我們獲得的標(biāo)識(shí)值是正確的，因?yàn)槲覀兲砑恿说谌齻€(gè)客戶條 目。如果我們檢查一下審計(jì)工作表，就會(huì)發(fā)現(xiàn)里面已經(jīng)有第四個(gè)條目記錄新插入的客戶記錄。由于函數(shù)SCOPE_IDENTITY()只作用于當(dāng)前范圍，只返 回當(dāng)前執(zhí)行程序的值，這樣就避免了發(fā)生剛才那樣的問題。
前面講過，函數(shù)@@IDENTITY和函數(shù)SCOPE_IDENTITY()不止用于表，不像函數(shù)IDENT_CURRENT那樣可以用表作為參 數(shù)。使用@@IDENTITY和SCOPE_IDENTITY()這兩個(gè)函數(shù)的話在設(shè)置代碼時(shí)需要加倍小心，才能夠從所需要的表里獲得正確的標(biāo)識(shí)值。從表 面上來看，放棄這兩個(gè)函數(shù)，只使用函數(shù)IDENT_CURRENT并指定表是更安全的辦法。這樣可以避免出現(xiàn)獲得錯(cuò)誤標(biāo)識(shí)值的情況，對(duì)吧?記得先前說過函 數(shù)IDENT_CURRENT不僅會(huì)跨范圍，而且它還會(huì)跨連接。也就是說，使用這個(gè)函數(shù)生成的值不僅僅限于你的連接所執(zhí)行的程序，它的涵蓋范圍還包括整個(gè) 數(shù)據(jù)庫所有的連接。因此，即使是在規(guī)模較小的OLTP環(huán)境里，它也會(huì)出現(xiàn)不能準(zhǔn)確返回所需值的問題。這樣就可能發(fā)生類似前面@@IDENTITY函數(shù)/觸 發(fā)器的數(shù)據(jù)損壞問題。
我的建議是函數(shù)SCOPE_IDENTITY()是三個(gè)函數(shù)里最安全的函數(shù)，應(yīng)該設(shè)置為默認(rèn)函數(shù)。使用這個(gè)函數(shù)，你可以放心地添加觸發(fā)器和次存儲(chǔ)過 程，無需擔(dān)心意外損壞數(shù)據(jù)。而另外兩個(gè)函數(shù)可以保留應(yīng)付特殊的情況，當(dāng)遇到需要使用這兩個(gè)函數(shù)的特殊情況時(shí)，建議記錄它們的使用情況并進(jìn)行測(cè)試。
小技巧：
Sql Server 判斷表是存在標(biāo)識(shí)列
If Exists(Select * from SysColumns Where ID=OBJECT_ID(N'TEST1') And COLUMNPROPERTY(ID,Name,'IsIdentity')=1)
Print N'有自增列'
Else
Print N'沒有自增列'
Sql Server 顯示當(dāng)前數(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自增長(zhǎng)字段復(fù)位方法：
SQLSERVER 復(fù)位:
Truncate table Ashare_CJHB
Dbcc checkident (Ashare_CJHB,RESEED,0)
獲取最后sql影響記錄的最后ID,慎用@@Identity 我們常用的獲sql影響的最后記錄id常用的是@@identity，但有些情況下會(huì)有問題，其實(shí)sql一共提供了3個(gè)不同的方法
SELECT SCOPE_IDENTITY()
select IDENT_CURRENT(TableName)
select @@IDENTITY
IDENT_CURRENT 類似于 SQL Server 2000 標(biāo)識(shí)函數(shù) SCOPE_IDENTITY 和 @@IDENTITY。這三個(gè)函數(shù)都返回最后生成的標(biāo)識(shí)值。但是，上述每個(gè)函數(shù)中定義的“最后”的作用域和會(huì)話有所不同。
IDENT_CURRENT 返回為某個(gè)會(huì)話和用域中的指定表生成的最新標(biāo)識(shí)值。
@@IDENTITY 返回為跨所有作用域的當(dāng)前會(huì)話中的某個(gè)表生成的最新標(biāo)識(shí)值。
SCOPE_IDENTITY 返回為當(dāng)前會(huì)話和當(dāng)前作用域中的某個(gè)表生成的最新標(biāo)識(shí)值。
在空表中調(diào)用 IDENT_CURRENT 函數(shù)時(shí)，此函數(shù)將返回 NULL。
如果語句和事務(wù)失敗，它們會(huì)更改表的當(dāng)前標(biāo)識(shí)，從而使標(biāo)識(shí)列中的值出現(xiàn)不連貫現(xiàn)象。即使未提交試圖向表中插入值的事務(wù)，也永遠(yuǎn)無法回滾標(biāo)識(shí)值。例 如，如果因 IGNORE_DUP_KEY 沖突而導(dǎo)致 INSERT 語句失敗，表的當(dāng)前標(biāo)識(shí)值仍然會(huì)增加。
@@IDENTITY 表示插入后新的表示種子值，例子如下：
假如已經(jīng)存在表job，向表插入一條數(shù)據(jù)，
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'//獲得表識(shí)值