轉： 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有三種不同的函式可以用來獲得含有標識列的表裡最後生成的標識值： @@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