在SQL语法提示工具SQL Prompt中,为什么应该避免使用@@IDENTITY函数?

SQL Prompt是一款实用的SQL语法提示工具。SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索,为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用,能大幅提高编码效率。此外,用户还可根据需要进行自定义,使之以预想的方式工作。

本文主要展示了为什么SQL Prompt有一个“最佳实践”规则(BP010)来检查@@ IDENTITY函数的使用情况,并建议使用一些不太容易出错的方法来获取表中使用的最新IDENTITY值。

@@IDENTITY函数返回IDENTITY在同一会话中创建的最后一个值。如果表上有触发器,或者表是复制中的发布,则该值有时可能是错误的。SQL Prompt的BP010代码分析规则将警告您,如果在SQL代码中检测到它的使用。

在SQL语法提示工具SQL Prompt中,为什么应该避免使用@@IDENTITY函数?

相比之下,SCOPE_IDENTITY()函数返回IDENTITY在同一范围内创建的最后一个标识,因此作为直接代替更安全。当一次插入多行时,重新设计代码,使用INSERT…OUTPUT来获取IDENTITY值和计算列。如果需要获取IDENTITY表的当前值,请使用IDENT_CURRENT('<tablename>')。

@@ IDENTITY的问题

IDENTITY列一般添加到表中,以保证对表的每一行的唯一引用。一个表只能有一个这样的列。它可以节省您从列或列组合创建自然的、唯一的键的麻烦。

IDENTITY列使用“seed”声明,第一个值将插入到列中,“increment”是应该添加到上一个值以创建下一个值的值。可以使用IDENT_SEED((<tablename>)函数来引用,而增量值可以通过IDENT_INCR(<tablename>)函数来引用。

在添加行时,表对象保留最初来自种子和增量值的“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;

在SQL语法提示工具SQL Prompt中,为什么应该避免使用@@IDENTITY函数?

为了避免这一切,只需获取最后一次插入的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');

在SQL语法提示工具SQL Prompt中,为什么应该避免使用@@IDENTITY函数?

如果将此输出插入到表变量中,则有很多机会使用IDENTITY列中的信息来使用该IDENTITY字段对具有对您插入的表的外键引用的关联表的填充。

在这种情况下,例如,我可能想要与此表相关的表,以提供名称的各个部分的含义,以便我可以分析所有圣徒的名字取自凯尔特神的名字(例如Lugh、Hus 、Brij或Finn),或者可能是与圣人相关的主要地点,它所代表的部落祖先,或提供好基督徒成为圣徒的日期。

结论

我们现在提供了更好的方法来处理IDENTITY列的流行使用,以提供一个主键,提供对行的简单唯一引用。在大多数情况下,@@IDENTITY函数都可以,但它有一个范围问题,可能让你感到困惑。在当下的热度中,很容易忘记您插入的表具有与之关联的触发器。最好养成使用SCOPE_IDENTITY替代或习惯使用功能更强大、更通用的OUTPUT条款的习惯,这些条款在最初设计@@IDENTITY的早期甚至没有想到。


上一篇:IdentityServer4搭建和使用


下一篇:Java中==和equals()之间有什么区别?