如何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
現在我們可以執行程序，創建客戶表的第一行了，以下是引用片段：
#p#
- 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)
【編輯推薦】
