Why You Should Avoid Using the @@IDENTITY Function
Why You Should Avoid Using the @@IDENTITY Function
See why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function.
Join the DZone community and get the full member experience.Join For Free
Phil Factor demonstrates why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function and suggests less error-prone ways to get the latest identity values used in a table.
@@IDENTITY function returns the last
IDENTITY value created in the same session. If you have a trigger on the table or if the table is a publication in a replication, then the value can sometimes be wrong. SQL Prompt's BP010 code analysis rule will warn you if it detects its use in your SQL code.
By contrast, the
SCOPE_IDENTITY() function returns the last
IDENTITY created in the same scope as well and is safer as a direct substitute. When inserting more than one row at a time, it might be even better to re-engineer the code to use
INSERT...OUTPUT to get
IDENTITY values and computed columns. If you need to get the current
IDENTITY value of a table, use
The problem with @@IDENTITY
IDENTITY column is generally added to a table to guarantee a unique reference to each row of a table. A table can have only one such column. It saves you the bother of having to create a natural, unique key from a column or combination of columns.
IDENTITY column is declared with the 'seed', the first value that will be inserted into the column, and the 'increment', the value it should add to the previous value to create the next value. The seed value can be referenced by using the
IDENT_SEED(<tablename>) function, and the increment value by the
As rows are added, the table object keeps the 'identity' value that came initially from the seed and the increment value and uses them to make sure that it provides the correct value for rows as they are inserted. Each number is used just once. You can then enforce uniqueness by using a unique constraint or unique index on this column.
You'd have thought it would be easy to find out the
IDENTITY values used when you insert into a table with an
IDENTITY column, but it isn't always so, and you can't assume an unbroken sequence either. Although the value that is inserted into an
IDENTITY is going to increment sequentially by the value you specify, it doesn't necessarily mean that your next
INSERT statement is going to be assigned the next value in the sequence because it may be assigned to an
INSERT performed in a different session. SQL Server is a multi-user system, so other users using the system simultaneously may 'steal' some of the values you are expecting by doing, or even attempting, an insertion, spoiling your sequence. Instead of an unbroken sequence, you could have gaps. This may be a problem if you are assigning meaning to that sequence.
@@IDENTITY contains the last
IDENTITY value that is generated by the preceding statement in the current session. If you are importing data that has to be placed in more than one table and these other tables contain foreign keys that reference the identity field, you'll need that value. Were it not for triggers or replication, you might have some confidence that this was the
IDENTITY value of the row you'd just inserted. If, however, the statement fires one or more triggers that perform inserts that, in turn, generate
IDENTITY values, then there is a risk that it won't because you no longer know for certain what the preceding
INSERT statement was.
Let's show that as simply as we can. We'll pretend we are creating a database of the 10,000 Irish Saints (there was some major devaluation of the requirements for sainthood for a while, in mediaeval Ireland). In the
Saints table, we try to record their names and meanings and a list of the Saints' days for each one. Each time we insert a new Saint, a trigger inserts the details of the individual days for each Saint in the
SaintsDay table, which also uses an
IDENTITY column as its primary key and has a foreign key reference to
Saints. A third table,
YearOfSainthood, records the year each saint was created and again has an
IDENTITY column as its primary key and a foreign key reference to
/* 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, we now innocently try to insert the details of a Saint and his year of Sainthood:
INSERT INTO saints([name], meaning,saintsDayList) VALUES ('Siadhal','','12 Feb, 8 Mar') INSERT INTO YearOfSainthood (Saint_id, [Year]) VALUES (@@Identity,'759')
That year of Sainthood would reference the wrong saint or no saint, but as we've got a foreign key constraint, it causes a foreign key constraint violation:
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 is not limited to a specific scope, meaning the module (stored procedure, trigger, function, or batch) that is currently executing and the trigger will be executed in the same session but a different scope. If a trigger inserts into another table that has an
@@IDENTITY returns the identity value of that subsequent insert. Likewise, if your database is part of a replication article, then the
@@IDENTITY value will be unreliable because it is used within the replication triggers and stored procedures.
In our example, it's simple to prove that
@@IDENTITY now shows the
IDENTITY field of the
SaintsDay table, not the
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, and just get the
IDENTITY value for the last insert, you should then be using the
SCOPE_IDENTITY() function syntax instead.
IDENTITY give you the value of the last
IDENTITY field assigned in the preceding statement in that session (either ignoring scope or within scope), you may decide that you need to know the
IDENTITY value of a specific table. If you do, then the
CURRENT() function gives this. You just specify the table name as a varchar.
Insert clause with output clause with IDENTITY
Although the simple advice for most uses of the
@@IDENTITY function is to replace it with
IDENTITY, one must say that, where you wish to determine the
IDENTITY values for an
INSERT statement that results in the insertion of several rows, the use of the
OUTPUT clause provides a safe way of finding the
IDENTITY values for every inserted row, along with any computed columns in case you need that as well, for some other purpose. Here is a simple example to illustrate the 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, you have many opportunities to use the information from the
IDENTITY column for the population of associated tables that have foreign key references to the table you've inserted into, using that
In this case, for example, I might want tables that relate to this table to provide meanings to the various parts of the name so I can analyze all the saints whose names are taken from the name of a Celtic god (e.g. Lugh, Hus, Brij, or Finn), or maybe the prime location associated with the saint, the tribal ancestor it represents, or provide the date at which the good Christian became a saint.
Nowadays, we are much better supplied with ways of dealing with the popular use of the
IDENTITY column to provide a primary key that provides a simple unique reference to rows. The
@@IDENTITY function is fine in most circumstances, but it has a scope problem that can catch you out. In the heat of the moment, it is too easy to forget that the table you are inserting into has a trigger associated with it. It is much better to get into the habit of using
SCOPE_IDENTITY instead or use the more powerful and versatile
OUTPUT clause that wasn't even imagined in those early days when
@@IDENTITY was first devised.
Published at DZone with permission of Phil Factor . See the original article here.
Opinions expressed by DZone contributors are their own.