This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Identity Crisis

Tom Moreau

Up until the release of SQL Server 2000, using IDENTITY columns on tables that had triggers was a bit tricky. If you didn't know what you were doing, you could get, well, "unexpected" results. This month, Dr. Tom Moreau investigates the IDENTITY column and the @@IDENTITY function.

The IDENTITY column solved a lot of problems for me on a project a number of years ago. We'd been using SQL Server 4.21a, and the application was generating keys through a key table. This had the effect of serializing transactions, since triggers would fire and need to pick up a key prior to inserting rows into other tables. Upgrading SQL Server to version 6.0 and later to 6.5 allowed us to take advantage of this great feature where it would auto-generate the keys and eliminate the bottleneck of the key table.

Often in IT, you solve one problem only to create another. I discovered this quite by accident on another project. The client wanted to audit all activity on a table that had an IDENTITY column, and I did this by using a trigger on the audited table. This trigger then inserted into a shadow table, which itself had an IDENTITY column. When I looked at the value of @@IDENTITY after the INSERT, I found I was getting the "wrong" value. It took a while, but I discovered that the value I was getting was that from the shadow table, not the audited table. As you can imagine, I thought I'd found a bug.

Fortunately for me, before proudly submitting this to the SQL Server team, I did a bit of research and landed upon Knowledge Base article Q163446, which discussed the issue, explaining that it wasn't a bug, but rather a trap. (See the sidebar for other Knowledge Base articles associated with IDENTITY and @@IDENTITY.) The @@IDENTITY global variable (referred to now as a system function—not a global variable) returns the value for the last inserted row by your session—regardless of the table into which it was inserted. In other words, if you have an IDENTITY column on Table A and one on Table B, with an INSERT trigger on Table A that inserts into Table B, the call to @@IDENTITY following the INSERT statement for Table A picks up the value for the INSERT into Table B—not Table A. The following code shows what I mean:

  insert TableA (ColA, ColB)
values ('This', 'That') -- trigger fires and
                        -- populates TableB

select @@IDENTITY       -- gets value for TableB

Table 1 lists some explicit errors you might encounter when dealing with the IDENTITY function and property. (Although similar, the IDENTITY function isn't the IDENTITY property that's used with CREATE TABLE and ALTER TABLE.)

Table 1. Error messages associated with the IDENTITY function and property.

Error number Severity level Message
8101 16 An explicit value for the identity column in table '%.*ls' can only be specified when a column list is used and IDENTITY_INSERT is ON.
8102 16 Cannot update identity column '%.*ls'.
8106 16 Table '%.*ls' does not have the identity property. Cannot perform SET operation.
8107 16 IDENTITY_INSERT is already ON for table '%.*ls.%.*ls.%.*ls'. Cannot perform SET operation for table '%.*ls'.
8108 16 Cannot add identity column, using the SELECT INTO statement, to table '%.*ls', which already has column '%.*ls' that inherits the identity property.
8109 16 Attempting to add multiple identity columns to table '%.*ls' using the SELECT INTO statement.
8147 16 Could not create IDENTITY attribute on nullable column '%.*ls', table '%.*ls'.

This, too, shall pass

Enter SQL Server 2000. This not-so-little annoyance has been overcome with the introduction of the SCOPE_IDENTITY() function, which takes no parameters and returns to you the IDENTITY value of the last inserted row within the current scope. (When a trigger is fired, the scope changes from that of the statement that fired the trigger to the scope of the trigger itself. Once the trigger has completed, the scope is returned.) Now, if you call SCOPE_IDENTITY()—not @@IDENTITY—you'll get the value arising from the INSERT you execute, not that of the underlying trigger.

Also introduced with SQL Server 2000 is the IDENT_CURRENT() function. This one takes a table name as a parameter and gives you back the value of the IDENTITY value of the last inserted row for the given table.

An example will help to clarify what happens when a trigger is fired and the trigger does an INSERT into tables that have IDENTITY properties. Suppose you have two tables—Raw and Summary—both of which have IDENTITY properties. The Raw table takes the individual entries from INSERT statements into that table. The IDENTITY column generates the keys. The number of rows inserted into the Raw table are contained in @@ROWCOUNT, and this is inserted into the Summary table. Thus, the entries in the Summary table keep track of the number of rows inserted into the Raw table for each individual INSERT.

Execute the script shown in Listing 1 while inside the Northwind database. This will make the numbers I present to you shortly make sense.

Listing 1. Table creation scripts for Raw and Summary tables.

  CREATE TABLE Raw
(
  ID      int       NOT NULL IDENTITY,
  RawData char (10) NOT NULL
)
GO
CREATE TABLE Summary
(
  ID        int       NOT NULL IDENTITY,
  TotalRows int       NOT NULL
)
GO
CREATE TRIGGER tri_Raw on Raw AFTER INSERT
AS
INSERT Summary (TotalRows) VALUES (@@ROWCOUNT)
GO

Now test the trigger with the code shown in Listing 2.

Listing 2. Testing a trigger on a table with an IDENTITY property.

  INSERT Raw
(
  RawData
)
SELECT
  'First'
FROM
  Northwind.dbo.sysobjects
WHERE
  type = 'U'
go
SELECT
  @@IDENTITY                 'IDENTITY',
  SCOPE_IDENTITY ()          'SCOPE IDENTITY',
  IDENT_CURRENT ('Summary')  'Summary',
  IDENT_CURRENT ('Raw')      'Raw'
go
INSERT Raw
(
  RawData
)
SELECT
  'Second'
FROM
  Northwind.dbo.sysobjects
WHERE
  type = 'S'
go
SELECT
  @@IDENTITY                 'IDENTITY',
  SCOPE_IDENTITY ()          'SCOPE IDENTITY',
  IDENT_CURRENT ('Summary')  'Summary',
  IDENT_CURRENT ('Raw')      'Raw'

The first INSERT puts a number of rows into the Raw table—one for every user table in sysobjects. The RawData column is populated with the constant "First." This causes the tri_Raw trigger to fire and populate one row in the Summary table, and this will cause the @@IDENTITY value to be 1, since the trigger placed one row into the Summary table and did so after the INSERT was carried out on the Raw table. The SCOPE_IDENTITY() value that's returned is 15, because you have that many user tables in the Northwind database. The two IDENT_CURRENT() calls return the values that correspond to @@IDENTITY and SCOPE_IDENTITY().

The second INSERT statement in Listing 2 is very similar to the first. This time, there's one row for every system table in the database, and the RawData column is populated with the constant "Second." Since the last table populated was Summary, and it now has only two rows, @@IDENTITY has a value of 2, while SCOPE_IDENTITY() now has increased by 19.

At last!

Well, as the saying goes, good things come to those who wait, and now the wait is over. You can go back into your code and get rid of all of those charming workarounds, thus giving you less to maintain. For more about the IDENTITY column, the IDENTITY() and @@IDENTITY() functions, the IDENTITY property, and hints on troubleshooting corrupt identity values, check out the book I co-authored with SQL Server MVP Itzik Ben-Gan, Advanced Transact-SQL for SQL Server 2000 (Apress).

Download TOM0401.SQL

  • Q163446 - PRB: Guarantee @@IDENTITY Value on a Per Table Basis
  • Q163447 - BUG: @@IDENTITY Variable Not Handled Like a Global Variable
  • Q190690 - INF: How to Set Up Replication on Tables with an Identity Column
  • Q198572 - BUG: IDENTITY Out of Sync If Server Is Shut Down w/o Checkpoint
  • Q234521 - BUG: Inserting Data into Temporary Table's Identity Column in Stored Procedure Fails
  • Q238673 - BUG: Package Execution Can Fail if Target Table Has an Identity
  • Q239667 - BUG: Design Table in SEM Does Not Preserve NFR Property for IDENTITY
  • Q243023 - PRB: Error 8104 Occurs with Non-Admin Owner Using Set IDENTITY_INSERT
  • Q263887 - BUG: Upgrade of an Empty Table Resets Identity Value to Seed
  • Q273586 - BUG: SELECT INTO With ORDER BY Clause Ignores Order By During the Insert

—Karen Watterson

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the April 2001 issue of Microsoft SQL Server Professional. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.