«
»

Using @@IDENTITY vs SCOPE_IDENTITY() and further implications with triggers

By Chris Gaskell

They say you learn something new every day – well here’s what I found out today.

I have been guilty of using

select @@IDENTITY

in stored procedures (SQL Server) to return the ID of the record just inserted when the table uses an auto number field as the primary key.

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. Always use SCOPE_IDENTITY() to return the identity of the recently added row.

This has issues when used in conjunction with triggers also. I have included an example below.

Triggers can be a useful mechanism for extending or integrating applications but the use of the above statement causes the original application problems in 2 ways:

  1. If the trigger inserts data into other tables the value is altered if the other table also uses auto numbering
  2. The original stored procedure starts to return dbNull rather than the ID

Sample Tables

CREATE TABLE dbo.Table1
(
id int NOT NULL IDENTITY (1, 1),
[desc] varchar(255) NULL,
what varchar(255) NULL
)

CREATE TABLE dbo.Table2
(
id int NOT NULL IDENTITY (1, 1),
[desc] varchar(255) NULL,
Extended varchar(255) NULL
)

Sample Stored Procedure

create procedure testing (@Desc varchar(255), @What varchar(255)) as
insert into table1 (desc, what) values (@Desc, @What)
select @@IDENTITY
return

Sample Trigger

CREATE TRIGGER Alert_Me ON [dbo].[tbl_sceneario]
FOR INSERT
AS

DECLARE @Desc varchar(255)

select @Desc = [Desc] from inserted

insert into table2 (desc, extended) values (@Desc, ‘Extended string’)

Problem 1.
@@Identity Changed

Because the trigger inserts into table2 which is using Auto numbered field it will set the @@Identity to its value.

Solution 1

To avoid this you should use SCOPE_IDENTITY()
This will return the last @@Identity generated by the stored procedure and not that from subsequent actions in the transaction

Problem 2.
Value returned is NULL

Due to the way SQL server operates, the trigger seems to affect @@IDENTITY and SCOPE_IDENTITY() and sets the value to NULL when returned as a rowset

Solution 2.

Add an output parameter to the procedure and set its value to SCOPE_IDENTITY

alter procedure testing (@Desc varchar(255), @What varchar(255), @ID int output) as
insert into table1 (desc, what) values (@Desc, @What)
set @ID = SCOPE_IDENTITY
return

This entry was posted on 15 November, 2006 at 1:27 pm and is filed under Development, SQL Server, Stored Procedures, TSql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

6 Responses to “Using @@IDENTITY vs SCOPE_IDENTITY() and further implications with triggers”

  1. Phil Winstanley Says:
    15 November, 2006 at 2:58 pm | Reply

    Yup, you should never use @@Identity.

  2. Eknath Says:
    22 February, 2007 at 2:10 pm | Reply

    Very good article, i solved my above same problem.Thanks!

  3. Cris Says:
    23 February, 2007 at 6:46 pm | Reply

    I´m having a problem whith this solution that you presented here.. I’m trying do insert in 4 tables, that depend one one other…in the first one it goes OK…the problem starts ind the second child…gives me the error

    “Cannot insert the value NULL into column ‘id’, table ‘Pedbo.tbl ‘; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.”

    I think the problem is becaus i’m trying to do all de inserts in the same function, but i’m not sure! So…is it possible to do moore than 1 insert on the same function?

    Best and regards
    Cris!

  4. angeliii Says:
    25 September, 2007 at 11:45 am | Reply

    just a remark about the second problem:

    create procedure testing (@Desc varchar(255), @What varchar(255)) as
    insert into table1 (desc, what) values (@Desc, @What)
    select @@IDENTITY
    return

    will return 2 recordsets for ado, the first one being empty and just containing the rowcount (1), and a second one, with the identity value.

    to supress the empty recordset in ado, simply use a SET NOCOUNT ON in the procedure:

    create procedure testing (@Desc varchar(255), @What varchar(255))
    as
    – supress empty recordset in ADO
    SET NOCOUNT ON
    – perform the insert
    insert into table1 (desc, what) values (@Desc, @What)
    – return the identity value generated
    select scope_identity() as new_id
    – done
    return

  5. Juan Says:
    21 November, 2007 at 9:47 am | Reply

    Maybe I can add something new just for thoroughness on this topic.

    I know I’m learning something new everyday as well…. I think programmers should have been doctors or lawyers. Work about the same, learn less, get paid more.

    To get identity information there are three options:

    1) @@IDENTITY
    2) SCOPE_IDENTITY()
    3) IDENT_CURRENT( [table_name])

    ==============
    FROM SQL SERVER BOOKS ONLINE:

    @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
    [...]
    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

    (Elsewhere:) A scope is a module: a stored procedure, trigger, function, or batch.

    SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.

    IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

    =========
    IDENT_CURRENT wouldn’t be suitable for retrieving the ID of the record just inserted in a specific table because it crosses sessions, but if you every wanted basically a snapshot of the “MAX” ID of a table, it would be suitable.

  6. Automobile Says:
    8 May, 2009 at 2:55 am | Reply

    Hello…

    Looking for something else, but nice site. Have an excellent day….

Leave a Reply