Phil Factor

29 July 2019

• SQL Prompt
• SQL Code Analysis

Why you Should Avoid Using the @@IDENTITY Function

The @@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, SCOPE_IDENTITY() function returns the last IDENTITY created in the same scope as well and so 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 IDENT_CURRENT('<tablename>').

The problem with @@IDENTITY

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

The 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 IDENT_INCR(<tablename>) function.

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 it 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 Saints.

Unaware of the existence of the trigger, we now innocently try to insert the details of a Saint and his year of Sainthood:

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

Unfortunately, @@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 column, @@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 Saints table:

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.

Whereas both @@IDENTITY and SCOPE_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 IDENT_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 SCOPE_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.

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 IDENTITY field.

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.

Conclusions

We are nowadays 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.