如何SQL Server中準(zhǔn)確的獲得標(biāo)識值
SQL Server有三種不同的函數(shù)可以用來獲得含有標(biāo)識列的表里最后生成的標(biāo)識值：
- @@IDENTITY
- SCOPE_IDENTITY()
- IDENT_CURRENT('數(shù)據(jù)表名')
以上三個函數(shù)雖然都可以返回?cái)?shù)據(jù)庫引擎最后生成插入標(biāo)識列的值，但是根據(jù)插入行的來源(例如：存儲過程或觸發(fā)器)以及插入該行的連接不同，這三個函數(shù)在功能上也有所不同。
@@IDENTITY函數(shù)可以返回所有范圍內(nèi)當(dāng)前連接插入最后所生成的標(biāo)識值(包括任何調(diào)用的存儲過程和觸發(fā)器)。這個函數(shù)不止可以適用于表。函數(shù)返回的值是最后表插入行生成的標(biāo)識值。
SCOPE_IDENTITY()函數(shù)跟上一個函數(shù)幾乎是一摸一樣的，不同的地方：即前者返回的值只限于當(dāng)前范圍(即執(zhí)行中的存儲過程)。
最后是IDENT_CURRENT函數(shù)，它可以用于所有范圍和所有連接，獲得最后生成的表標(biāo)識值。跟前面兩個函數(shù)不同的是，這個函數(shù)只用于表，并且使用[數(shù)據(jù)表名]作為一個參數(shù)。
我們可以舉實(shí)例來演示上述函數(shù)是如何運(yùn)作的。
首先，我們創(chuàng)建兩個簡單的例表：一個代表客戶表，一個代表審計(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
然后，我們還要創(chuàng)建一個存儲過程和一個輔助觸發(fā)器，這個存儲過程將在數(shù)據(jù)庫表里插入新的客戶行，并返回生成的標(biāo)識值，而觸發(fā)器則會向?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)建客戶表的第一行了，以下是引用片段：
#p#
- DECLARE @customerid INT
- EXEC dbo.p_InsertCustomer @customerid output
- SELECT @customerid AS customerid
執(zhí)行后返回了我們需要的第一個客戶的值，并記錄了插入審計(jì)表的條目。到目前為止，數(shù)據(jù)顯示沒有任何問題。
假設(shè)由于先前溝通出現(xiàn)了偏差，一個客戶服務(wù)代表現(xiàn)在需要從數(shù)據(jù)庫里刪除掉這個新增的客戶。我們現(xiàn)在就來把新插入的客戶行刪除掉：
以下是引用片段：
- DELETE FROM dbo.customer WHERE customerid = 1
現(xiàn)在，客戶工作表為空表，而審計(jì)工作表里則有兩行——第一行是記錄第一次插入行，第二行是記錄刪除客戶記錄。
現(xiàn)在我們再往數(shù)據(jù)庫里增加第二個客戶信息并檢測一下獲得的標(biāo)識值：
以下是引用片段：
- DECLARE @customerid INT
- EXEC dbo.p_InsertCustomer @customerid output
- SELECT @customerid AS customerid
哇!看看出現(xiàn)了什么情況!如果我們現(xiàn)在再看客戶工作表，就會發(fā)現(xiàn)雖然創(chuàng)建了客戶2，但是我們的程序返回的標(biāo)識值為3!到底出了什么問題呢?回想一下，前面講過@@IDENTITY函數(shù)的作用范圍，它會返回主程序調(diào)用的任何存儲過程或觸動任何觸發(fā)器最后生成的標(biāo)識值，取決于哪一個在函數(shù)被調(diào)用前最后生成標(biāo)識值。在我們的例子里，初始范圍是p_InsertCustomer，然后是觸發(fā)器用來記錄插入條目的tr_customer_log。因此我們返回獲得的標(biāo)識值是審計(jì)工作表里觸發(fā)器插入生成的標(biāo)識值，而不是我們想要的客戶工作表里的生成的標(biāo)識值。
在SQL Server 2000之前的版本，@@IDENTITY函數(shù)是獲得標(biāo)識值的唯一方法。由于會出現(xiàn)這樣的存儲過程/觸發(fā)器問題，SQL Server開發(fā)團(tuán)隊(duì)在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT這兩個函數(shù)來解決這個問題。所以在舊的SQL Server版本里，要解決這個問題比較麻煩。如果是SQL Server6.5版本，我建議可以去掉標(biāo)識列，然后創(chuàng)建一個可以包含下一個需要使用的值的輔助表，可以達(dá)到標(biāo)識列的作用效果。不過這個辦法也不是什么高明的辦法。
現(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
我們返回的標(biāo)識值還是3，不過這次我們獲得的標(biāo)識值是正確的，因?yàn)槲覀兲砑恿说谌齻€客戶條目。如果我們檢查一下審計(jì)工作表，就會發(fā)現(xiàn)里面已經(jīng)有第四個條目記錄新插入的客戶記錄。由于函數(shù)SCOPE_IDENTITY()只作用于當(dāng)前范圍，只返回當(dāng)前執(zhí)行程序的值，這樣就避免了發(fā)生剛才那樣的問題。
前面講過，函數(shù)@@IDENTITY和函數(shù)SCOPE_IDENTITY()不止用于表，不像函數(shù)IDENT_CURRENT那樣可以用表作為參數(shù)。使用@@IDENTITY和SCOPE_IDENTITY()這兩個函數(shù)的話在設(shè)置代碼時需要加倍小心，才能夠從所需要的表里獲得正確的標(biāo)識值。從表面上來看，放棄這兩個函數(shù)，只使用函數(shù)IDENT_CURRENT并指定表是更安全的辦法。這樣可以避免出現(xiàn)獲得錯誤標(biāo)識值的情況，對吧?記得先前說過函數(shù)IDENT_CURRENT不僅會跨范圍，而且它還會跨連接。也就是說，使用這個函數(shù)生成的值不僅僅限于你的連接所執(zhí)行的程序，它的涵蓋范圍還包括整個數(shù)據(jù)庫所有的連接。因此，即使是在規(guī)模較小的OLTP環(huán)境里，它也會出現(xiàn)不能準(zhǔn)確返回所需值的問題。這樣就可能發(fā)生類似前面@@IDENTITY函數(shù)/觸發(fā)器的數(shù)據(jù)損壞問題。
我的建議是函數(shù)SCOPE_IDENTITY()是三個函數(shù)里最安全的函數(shù)，應(yīng)該設(shè)置為默認(rèn)函數(shù)。使用這個函數(shù)，你可以放心地添加觸發(fā)器和次存儲過程，無需擔(dān)心意外損壞數(shù)據(jù)。而另外兩個函數(shù)可以保留應(yīng)付特殊的情況，當(dāng)遇到需要使用這兩個函數(shù)的特殊情況時，建議記錄它們的使用情況并進(jìn)行測試。
小技巧：
Sql Server 判斷表是存在標(biāo)識列
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自增張字段復(fù)位方法：
SQLSERVER 復(fù)位:
Truncate table Ashare_CJHB
Dbcc checkident (Ashare_CJHB,RESEED,0)
【編輯推薦】
