SQL語法提示工具SQL Prompt教程：避免使用@@IDENTITY函數(shù)的原因

SQL Prompt是一款實(shí)用的SQL語法提示工具。SQL Prompt根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進(jìn)行檢索，為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當(dāng)開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt免費(fèi)版

本文主要展示了為什么SQL Prompt有一個“最佳實(shí)踐”規(guī)則（BP010）來檢查@@ IDENTITY函數(shù)的使用情況，并建議使用一些不太容易出錯的方法來獲取表中使用的最新IDENTITY值。

@@IDENTITY函數(shù)返回IDENTITY在同一會話中創(chuàng)建的最后一個值。如果表上有觸發(fā)器，或者表是復(fù)制中的發(fā)布，則該值有時可能是錯誤的。SQL Prompt的BP010代碼分析規(guī)則將警告您，如果在SQL代碼中檢測到它的使用。

相比之下，SCOPE_IDENTITY()函數(shù)返回IDENTITY在同一范圍內(nèi)創(chuàng)建的最后一個標(biāo)識，因此作為直接代替更安全。當(dāng)一次插入多行時，重新設(shè)計(jì)代碼，使用INSERT…OUTPUT來獲取IDENTITY值和計(jì)算列。如果需要獲取IDENTITY表的當(dāng)前值，請使用IDENT_CURRENT('

@@ IDENTITY的問題

IDENTITY列一般添加到表中，以保證對表的每一行的唯一引用。一個表只能有一個這樣的列。它可以節(jié)省您從列或列組合創(chuàng)建自然的、唯一的鍵的麻煩。

IDENTITY列使用“seed”聲明，第一個值將插入到列中，“increment”是應(yīng)該添加到上一個值以創(chuàng)建下一個值的值?？梢允褂肐DENT_SEED(

在添加行時，表對象保留最初來自種子和增量值的“identity”值，并使用它們來確保在插入行時為行提供正確的值。每個數(shù)字只使用一次。然后，可以通過在此列上使用唯一約束或唯一索引來強(qiáng)制實(shí)現(xiàn)唯一性。

您可能認(rèn)為，在插入帶有IDENTITY列的表時，很容易找到所使用的IDENTITY值，但并不總是這樣，并且您也不能假設(shè)一個完整的序列。盡管插入到IDENTITY中的值將按指定的值順序遞增，但這并不一定意味著您的下一個INSERT語句將被分配給序列中的下一個值，因?yàn)樗赡鼙环峙浣oINSERT在不同的會話中執(zhí)行。SQL Server是一個多用戶系統(tǒng)，因此同時使用該系統(tǒng)的其他用戶可能通過執(zhí)行，甚至嘗試插入，破壞序列來“竊取”您期望的某些值。您可以有間隙，而不是一個完整的序列。如果要為該序列指定含義，這可能是一個問題。

@@IDENTITY包含當(dāng)前會話中前一個語句生成的最后一個IDENTITY值。如果要導(dǎo)入必須放在多個表中的數(shù)據(jù)，并且這些其他表包含引用標(biāo)識字段的外鍵，則需要該值。如果不是觸發(fā)器或復(fù)制，您可能會確信這是您剛剛插入的行的IDENTITY值。但是，如果語句觸發(fā)一個或多個執(zhí)行插入的觸發(fā)器，而這些觸發(fā)器又生成IDENTITY值，則存在不會發(fā)生的風(fēng)險，因?yàn)槟辉俅_切知道前面的INSERT語句是什么。

讓我們盡可能地表明。我們假設(shè)正在創(chuàng)建一個包含10000名愛爾蘭圣徒的數(shù)據(jù)庫（在中世紀(jì)的愛爾蘭，有一段時間內(nèi)對圣徒身份的要求大幅降低）。在Saints表格中，我們嘗試記錄他們的名字和含義，以及每個人的圣徒日期列表。每次我們插入一個新的Saint時，觸發(fā)器會在SaintsDay表中插入每個圣徒的具體日期，該表也使用IDENTITY列作為其主鍵，并具有對Saint的外鍵引用。第三個表，YearOfSainthood記錄每個圣人創(chuàng)建的年份，并再次將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])

不知道觸發(fā)器的存在，現(xiàn)在天真地試圖插入圣徒和他的圣徒年的細(xì)節(jié)：

12345    INSERT INTO saints([name], meaning,saintsDayList) 
VALUES ('Siadhal','','12 Feb, 8 Mar') 

 INSERT INTO YearOfSainthood (Saint_id, [Year])
VALUES (@@Identity,'759')

Sainthood的那一年會引用錯誤的圣人，或者沒有圣人，但是因?yàn)槲覀冇幸粋€外鍵約束，它會導(dǎo)致外鍵約束違規(guī)：

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不限于特定范圍，即當(dāng)前正在執(zhí)行的模塊（存儲過程、觸發(fā)器、函數(shù)或批處理），觸發(fā)器將在同一會話中執(zhí)行但范圍不同。如果觸發(fā)器插入到具有IDENTITY列的另一個表中，則@@IDENTITY將返回后續(xù)插入的標(biāo)識值。同樣，如果您的數(shù)據(jù)庫是復(fù)制文章的一部分，那么該@@IDENTITY值將不可靠，因?yàn)樗窃趶?fù)制觸發(fā)器和存儲過程中使用的。

在我們的示例中，很容易證明@@IDENTITY現(xiàn)在顯示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值，然后應(yīng)該使用SCOPE_IDENTITY()函數(shù)語法。

雖然@@IDENTITY和SCOPE_ IDENTITY都為您提供了該會話中前一個語句中指定的最后一個IDENTITY字段的值（忽略范圍或范圍內(nèi)），但您可能決定需要知道特定表的IDENTITY值。如果你這樣做，那么IDENT_ CURRENT（）函數(shù)會給出這個。您只需將表名指定為varchar即可。

帶有IDENTITY的輸出子句的Insert子句

盡管對@@IDENTITY函數(shù)的大多數(shù)用途的簡單建議是用SCOPE_ IDENTITY替換它，但必須要說的是，在您希望確定導(dǎo)致插入多行的INSERT語句的IDENTITY值的情況下，使用OUTPUT子句提供了一種安全的方法，可以為每個插入的查找IDENTITY值，以及任何計(jì)算列，以防您需要它們。這是一個簡單的例子來說明這一點(diǎn)。

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');

如果將此輸出插入到表變量中，則有很多機(jī)會使用IDENTITY列中的信息來使用該IDENTITY字段對具有對您插入的表的外鍵引用的關(guān)聯(lián)表的填充。

在這種情況下，例如，我可能想要與此表相關(guān)的表，以提供名稱的各個部分的含義，以便我可以分析所有圣徒的名字取自凱爾特神的名字（例如Lugh、Hus 、Brij或Finn），或者可能是與圣人相關(guān)的主要地點(diǎn)，它所代表的部落祖先，或提供好基督徒成為圣徒的日期。

結(jié)論

我們現(xiàn)在提供了更好的方法來處理IDENTITY列的流行使用，以提供一個主鍵，提供對行的簡單唯一引用。在大多數(shù)情況下，@@IDENTITY函數(shù)都可以，但它有一個范圍問題，可能讓你感到困惑。在當(dāng)下的熱度中，很容易忘記您插入的表具有與之關(guān)聯(lián)的觸發(fā)器。最好養(yǎng)成使用SCOPE_IDENTITY替代或習(xí)慣使用功能更強(qiáng)大、更通用的OUTPUT條款的習(xí)慣，這些條款在最初設(shè)計(jì)@@IDENTITY的早期甚至沒有想到。

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊“咨詢在線客服”

掃描關(guān)注慧聚IT微信公眾號，及時獲取最新動態(tài)及最新資訊