SQL語法提示工具SQL Prompt教程：避免使用@@IDENTITY函數的原因
SQL Prompt是一款實用的SQL語法提示工具。SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據需要進行自定義，使之以預想的方式工作。
本文主要展示了為什么SQL Prompt有一個“最佳實踐”規則（BP010）來檢查@@ IDENTITY函數的使用情況，并建議使用一些不太容易出錯的方法來獲取表中使用的最新IDENTITY值。
@@IDENTITY函數返回IDENTITY在同一會話中創建的最后一個值。如果表上有觸發器，或者表是復制中的發布，則該值有時可能是錯誤的。SQL Prompt的BP010代碼分析規則將警告您，如果在SQL代碼中檢測到它的使用。
相比之下，SCOPE_IDENTITY()函數返回IDENTITY在同一范圍內創建的最后一個標識，因此作為直接代替更安全。當一次插入多行時，重新設計代碼，使用INSERT…OUTPUT來獲取IDENTITY值和計算列。如果需要獲取IDENTITY表的當前值，請使用IDENT_CURRENT('
@@ IDENTITY的問題
IDENTITY列一般添加到表中，以保證對表的每一行的唯一引用。一個表只能有一個這樣的列。它可以節省您從列或列組合創建自然的、唯一的鍵的麻煩。
IDENTITY列使用“seed”聲明，第一個值將插入到列中，“increment”是應該添加到上一個值以創建下一個值的值。可以使用IDENT_SEED(
在添加行時，表對象保留最初來自種子和增量值的“identity”值，并使用它們來確保在插入行時為行提供正確的值。每個數字只使用一次。然后，可以通過在此列上使用唯一約束或唯一索引來強制實現唯一性。
您可能認為，在插入帶有IDENTITY列的表時，很容易找到所使用的IDENTITY值，但并不總是這樣，并且您也不能假設一個完整的序列。盡管插入到IDENTITY中的值將按指定的值順序遞增，但這并不一定意味著您的下一個INSERT語句將被分配給序列中的下一個值，因為它可能被分配給INSERT在不同的會話中執行。SQL Server是一個多用戶系統，因此同時使用該系統的其他用戶可能通過執行，甚至嘗試插入，破壞序列來“竊取”您期望的某些值。您可以有間隙，而不是一個完整的序列。如果要為該序列指定含義，這可能是一個問題。
@@IDENTITY包含當前會話中前一個語句生成的最后一個IDENTITY值。如果要導入必須放在多個表中的數據，并且這些其他表包含引用標識字段的外鍵，則需要該值。如果不是觸發器或復制，您可能會確信這是您剛剛插入的行的IDENTITY值。但是，如果語句觸發一個或多個執行插入的觸發器，而這些觸發器又生成IDENTITY值，則存在不會發生的風險，因為您不再確切知道前面的INSERT語句是什么。
讓我們盡可能地表明。我們假設正在創建一個包含10000名愛爾蘭圣徒的數據庫（在中世紀的愛爾蘭，有一段時間內對圣徒身份的要求大幅降低）。在Saints表格中，我們嘗試記錄他們的名字和含義，以及每個人的圣徒日期列表。每次我們插入一個新的Saint時，觸發器會在SaintsDay表中插入每個圣徒的具體日期，該表也使用IDENTITY列作為其主鍵，并具有對Saint的外鍵引用。第三個表，YearOfSainthood記錄每個圣人創建的年份，并再次將IDENTITY列作為其主鍵和對Saints的外鍵引用。
/* drop our objects if they already exist */
IF Object_Id('dbo.YearOfSainthood') IS not NULL
DROP TABLE dbo.YearOfSainthood
IF Object_Id('dbo.saintsDay') IS not NULL
DROP TABLE dbo.saintsDay
IF Object_Id('dbo.saints') IS not NULL
DROP TABLE dbo.Saints
go
/* create a new name/meaning table for our Irish Saints */
CREATE TABLE dbo.Saints
(
Saint_id INT IDENTITY(1, 1) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
meaning VARCHAR(80) NOT NULL,
SaintsDayList VARCHAR(4000) null
);
/* and create a new Date table for the saints days
associated with the saint name */
CREATE TABLE dbo.SaintsDay
(
SaintsDay_id INT IDENTITY PRIMARY KEY,
Saint_ID INT NOT NULL FOREIGN KEY REFERENCES dbo.Saints(Saint_id),
DayAsString VARCHAR(500) NOT NULL,
TheMonth INT NULL,
TheDay INT NULL
);
GO
CREATE TABLE dbo.YearOfSainthood
(
SainthoodYear_id INT IDENTITY PRIMARY KEY,
Saint_ID INT NOT NULL FOREIGN KEY REFERENCES dbo.Saints(Saint_id),
[Year] int
);
go
/* create a trigger that takes the list of saints' days and
inserts them into a relational table */
CREATE TRIGGER GrabTheSaintsDays
ON dbo.saints
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.SaintsDay(Saint_id, DayAsString)
SELECT saint_id, LTrim(value)
FROM inserted
OUTER APPLY STRING_SPLIT ( inserted.[SaintsDayList] , ',' )
END
GO
-- Now INSERT a set of values into the saints table
INSERT INTO saints([name], meaning,saintsDayList)
SELECT [Name], Meaning,[Saints days] FROM (VALUES
('Cruimín','crooked; bent','28 Jun'),
('Díocuill','?','17 Nov, 1 May, 28 Feb'),
('Fursa','?','16 Jun'),
('Faolchú','wolf; wolf-hound','23 May'),
('Líthghein','born with luck & prosperity','16 Jan'),
('Díomán','pet form of Diarmaid','10 Jan'),
('Onchú','fierce hound','9 Jul'),
('Fionbharr','fair-haired','4 Aug, 25 Aug, 9 Sep, 10 Sep, 25 Sep'),
('Darearca','daughter of Erc','15 Jan, 9 Sep'),
('énán','?','29 Apr, 30 Jan'),
('Brógán','?','1 Jan, 9 Apr, 27 Jun, 8 Jul, 25 Aug, 21 Sep'),
('Faoiltiarn','lord of wolves','17 Mar'),
('Daghán','good','12 Mar, 13 Sep'),
('Laoire','calf-herd','11 May'),
('Beoc','?','16 Dec'),
('Séanait','hawk','18 Dec'),
('Brígh','high; noble','31 Jan'),
('Dúinseach','fortress?','12 Dec, 5 Aug'),
('Tuaimmíne','variant of Tómmán','12 Jun, 10 Jan'),
('Fínín','wine-birth','5 Feb'),
('Lonán','blackbird','6 Jun, 22 Jan, 7 Feb, 11 Jul, 2 Aug, 24 Sep, 1 Nov, 12 Nov'),
('Breac','freckled','15 Jan'),
('Scoithín','bloom; blossom','2 Jan'),
('Teimhnín','dark','7 Aug, 17 Aug'),
('Aoidhghean','"born of Aodh"','1 May'),
('Ceallach','bright-headed?','1 Apr, 7 Apr, 18 Jul, 7 Oct'),
('Fiachra','Battle-king?','8 Feb, 2 May, 25 Jul, 30 Aug, 28 Sep'),
('Iobhar','yew','23 Apr'),
('Conna','pet form of Colmán (''dove'')','3 Feb')
)f([Name], Meaning,[Saints days])不知道觸發器的存在，現在天真地試圖插入圣徒和他的圣徒年的細節：
12345 INSERT INTO saints([name], meaning,saintsDayList)
VALUES ('Siadhal','','12 Feb, 8 Mar')
INSERT INTO YearOfSainthood (Saint_id, [Year])
VALUES (@@Identity,'759')Sainthood的那一年會引用錯誤的圣人，或者沒有圣人，但是因為我們有一個外鍵約束，它會導致外鍵約束違規：
Msg 547, Level 16, State 0, Line 89 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__YearOfSai__Saint__3F3159AB". The conflict occurred in database "master", table "dbo.Saints", column 'Saint_id'.
遺憾的是，@@IDENTITY不限于特定范圍，即當前正在執行的模塊（存儲過程、觸發器、函數或批處理），觸發器將在同一會話中執行但范圍不同。如果觸發器插入到具有IDENTITY列的另一個表中，則@@IDENTITY將返回后續插入的標識值。同樣，如果您的數據庫是復制文章的一部分，那么該@@IDENTITY值將不可靠，因為它是在復制觸發器和存儲過程中使用的。
在我們的示例中，很容易證明@@IDENTITY現在顯示SaintsDay表的IDENTITY字段而不是Saints表：
SELECT @@Identity AS [Value of @@Identity],
Scope_Identity() AS [Value of scope_Identity],
Max(Saint_id) AS [Largest ID Assigned],
Ident_Current('dbo.saints') AS [Identity value of 'saints'],
Ident_Current('dbo.saintsDay') AS [Identity value of 'saintsDay']
FROM Saints;
為了避免這一切，只需獲取最后一次插入的IDENTITY值，然后應該使用SCOPE_IDENTITY()函數語法。
雖然@@IDENTITY和SCOPE_ IDENTITY都為您提供了該會話中前一個語句中指定的最后一個IDENTITY字段的值（忽略范圍或范圍內），但您可能決定需要知道特定表的IDENTITY值。如果你這樣做，那么IDENT_ CURRENT（）函數會給出這個。您只需將表名指定為varchar即可。
帶有IDENTITY的輸出子句的Insert子句
盡管對@@IDENTITY函數的大多數用途的簡單建議是用SCOPE_ IDENTITY替換它，但必須要說的是，在您希望確定導致插入多行的INSERT語句的IDENTITY值的情況下，使用OUTPUT子句提供了一種安全的方法，可以為每個插入的查找IDENTITY值，以及任何計算列，以防您需要它們。這是一個簡單的例子來說明這一點。
CREATE TABLE #IrishSaintsDays ( Saint_id INT IDENTITY, name NVARCHAR(50) NOT NULL, CurrentsaintsDate DATETIME2(7) NULL, SaintsDay AS Convert(VARCHAR(6), CurrentsaintsDate, 113) ); INSERT INTO #IrishSaintsDays (name, CurrentsaintsDate) OUTPUT inserted.Saint_id, inserted.name, Inserted.SaintsDay VALUES (N'Finten, also Fintan, Munnu', '2019-10-21T00:00:00'), (N'énda mac Conaill', '2019-03-21T00:00:00'), (N'Olcán', '2019-02-20T00:00:00'), (N'Suibne moccu Urthrí', NULL), (N'Coirpre Crom mac Feradaig', '2019-03-06T00:00:00'), (N'Béoáed mac Ocláin', '2019-03-07T00:00:00'), (N'Cairech Dergain', '2019-02-09T00:00:00'), (N'Gobban Find mac Lugdach', NULL), (N'Fáelán Amlabar, Fillan', '2019-06-20T00:00:00'), (N'Commán mac Fáelchon, Mo Chommóc', '2019-12-26T00:00:00'), (N'Boethian of Pierrepoint', NULL), (N'Caomhán (Cavan, Kevin)', '2019-06-14T00:00:00'), (N'Manchán of Mohill (Manchán of Maothail)', '2019-02-25T00:00:00'), (N'Columba', NULL), (N'Raoiriú', NULL), (N'Dublitter', '2019-05-15T00:00:00'), (N'Cuimín of Kilcummin', NULL), (N'Fínán Cam mac Móenaig', '2019-04-07T00:00:00'), (N'Maonacan of Athleague', '2019-02-18T00:00:00'), (N'Scuithin', '2019-01-02T00:00:00');
如果將此輸出插入到表變量中，則有很多機會使用IDENTITY列中的信息來使用該IDENTITY字段對具有對您插入的表的外鍵引用的關聯表的填充。
在這種情況下，例如，我可能想要與此表相關的表，以提供名稱的各個部分的含義，以便我可以分析所有圣徒的名字取自凱爾特神的名字（例如Lugh、Hus 、Brij或Finn），或者可能是與圣人相關的主要地點，它所代表的部落祖先，或提供好基督徒成為圣徒的日期。
結論
我們現在提供了更好的方法來處理IDENTITY列的流行使用，以提供一個主鍵，提供對行的簡單唯一引用。在大多數情況下，@@IDENTITY函數都可以，但它有一個范圍問題，可能讓你感到困惑。在當下的熱度中，很容易忘記您插入的表具有與之關聯的觸發器。最好養成使用SCOPE_IDENTITY替代或習慣使用功能更強大、更通用的OUTPUT條款的習慣，這些條款在最初設計@@IDENTITY的早期甚至沒有想到。
想要購買SQL Prompt正版授權，或了解更多產品信息請點擊
掃描關注慧聚IT微信公眾號，及時獲取最新動態及最新資訊
QQ交談
在線咨詢
渝公網安備
50010702500608號
客服熱線