SQL Prompt It is a practical SQL grammar prompt tool. SQL Prompt automatically retrieves the database object name, grammar and code fragments to provide users with appropriate code selection. Automatic script settings make code easy to read -- especially when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency. In addition, users can also customize it to work in the desired way according to their needs.

This article mainly shows why SQL Prompt has a "best practice" rule (BP010) to check the use of @@ IDENTITY functions, and suggests some less error-prone methods to get the latest IDENTITY values used in tables.

@@ The IDENTITY function returns the last value created by IDENTITY in the same session. If there is a trigger on the table, or if the table is a publication in replication, the value may sometimes be wrong. The BP010 Code Analysis Rule of SQL Prompt will warn you if its use is detected in the SQL code.

By contrast, the SCOPE_IDENTITY() function returns the last identifier created by IDENTITY within the same scope, so it is safer as a direct replacement. When inserting multiple lines at a time, redesign the code, using INSERT... OUTPUT to get IDENTITY values and compute columns. If you need to get the current value of the IDENTITY table, use IDENT_CURRENT ('<tablename>').

@@ IDENTITY Problem

IDENTITY columns are generally added to tables to ensure a unique reference to each row of the table. A table can only have one such column. It saves you the trouble of creating natural, unique keys from columns or column combinations.

The IDENTITY column uses the seed declaration, the first value is inserted into the column, and increment is the value that should be added to the previous value to create the next value. You can use the IDENT_SEED (< tablename >) function to reference, and the incremental value can be referenced by the IDENT_INCR (< tablename >) function.

When adding rows, table objects retain the "identity" values originally derived from seeds and increments, and use them to ensure that the rows are provided with the correct values when inserting rows. Each number is used only once. Then, uniqueness can be enforced by using unique constraints or unique indexes on this column.

You might think that when inserting tables with IDENTITY columns, it's easy to find the IDENTITY values used, but that's not always the case, and you can't assume a complete sequence. Although the values inserted into IDENTITY will increase in the specified order of values, this does not necessarily mean that your next INSERT statement will be assigned to the next value in the sequence, because it may be assigned to INSERT for execution in different sessions. SQL Server is a multi-user system, so other users who use the system at the same time may "steal" some values you expect by executing, or even trying to insert, destroy sequences. You can have gaps, not a complete sequence. This may be a problem if you want to specify meaning for the sequence.

@@ IDENTITY contains the last IDENTITY value generated by the previous statement in the current session. This value is required if you want to import data that must be placed in multiple tables, and these other tables contain foreign keys that refer to identifying fields. If it is not a trigger or replication, you may be convinced that this is the IDENTITY value of the row you just inserted. However, if a statement triggers one or more triggers to perform insertion and these triggers generate IDENTITY values, there is a risk that this will not happen because you no longer know exactly what the previous INSERT statement is.

Let's make it as clear as possible. We assume that a database of 10,000 Irish saints is being created (in medieval Ireland, the requirement for sainthood was drastically reduced for some time). In the Saints table, we try to record their names and meanings, as well as a list of saints'dates for each person. Each time we insert a new Saint, the trigger inserts a specific date for each saint in the Saints Day table, which also uses the IDENTITY column as its primary key and has a foreign key reference to Saint. The third table, YearOfSainthood, records the year each Saint created, and again uses the IDENTITY column as its primary key and a foreign key reference to 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])

Unaware of the existence of the trigger, now naively attempts to insert details of the year of the saint and his saints:

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

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

In Sainthood's year, incorrect saints or no saints were used, but because we have a foreign key constraint, it can lead to foreign key constraint violations:

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'.

Unfortunately, @@IDENTITY is not limited to a specific scope, i.e. modules currently being executed (stored procedures, triggers, functions or batches), triggers will execute in the same session but in different ranges. If the trigger is inserted into another table with an IDENTITY column, then @@IDENTITY returns the subsequent inserted identifier value. Similarly, if your database is part of a replication article, the @@IDENTITY value will be unreliable because it is used in replication triggers and stored procedures.

In our example, it's easy to prove that @@IDENTITY now displays the IDENTITY field of the SaintsDay table instead of the Saints table:

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;

To avoid all this, you just need to get the last insert value of IDENTITY, and then you should use the SCOPE_IDENTITY() functional grammar.

Although both @@IDENTITY and SCOPE_ IDENTITY provide you with the value of the last IDENTITY field specified in the previous statement in the session (ignoring scope or scope), you may decide that you need to know the IDENTITY value of a particular table. If you do this, the IDENT CURRENT () function will give you this. You just need to specify the table name as varchar.

Insert clause with IDENTITY output clause

Although the simple recommendation for most uses of the @@IDENTITY function is to replace it with SCOPE_ IDENTITY, it must be said that the use of the OUTPUT clause provides a secure way to find the IDENTITY value for each insert, as well as any arbitrary value, when you want to determine the IDENTITY value that causes the insertion of a multi-line INSERT statement. How to calculate columns in case you need them? This is a simple example to illustrate this point.

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

If you insert this output into a table variable, there are many opportunities to use the information in the IDENTITY column to populate an associated table with a foreign key reference to the table you insert.

In this case, for example, I might want a table related to this table to provide the meanings of the various parts of the name so that I can analyze that all saints'names are taken from the names of Celtic gods (such as Lugh, Hus, Brij or Finn), or that they may be the main sites associated with saints, the tribal ancestors they represent. Or provide a good date for Christians to become saints.

conclusion

We now provide a better way to deal with the popular use of IDENTITY columns to provide a primary key that provides simple, unique references to rows. In most cases, the @@IDENTITY function works, but it has a scope problem that may confuse you. In the current heat, it's easy to forget that the tables you insert have triggers associated with them. It's best to develop the habit of using SCOPE_IDENTITY instead of using more powerful and general OUTPUT clauses, which were not even thought of in the early days of the original design @@IDENTITY.