scope_identity vs ident_current

Question

After much research I am a little confused by which identity tracker I should use in sql.

From what I understand scope_identity will give me the last id updated from any table and ident_current will will return the last id from a specified table.

So given that information it would seem to me the best version to use (if you know which table you will be updating) is ident_current. Yet, upon reading it seems most people prefer to use scope_identity. What is the reasoning behind this and is there a flaw in my logic?

Answer 1

See this blogpost for your answer in detail. Scope_identity will never return identities due to inserts done by triggers. It wont be a great idea to use ident_current in a world of change where tablenames are changed..like in a dev env.

Answer 2

/*
* IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
* @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
* SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
*/

IF OBJECT_ID(N't6', N'U') IS NOT NULL
    DROP TABLE t6 ;
GO
IF OBJECT_ID(N't7', N'U') IS NOT NULL
    DROP TABLE t7 ;
GO
CREATE TABLE t6 (id INT IDENTITY) ;
CREATE TABLE t7
    (
     id INT IDENTITY(100, 1)
    ) ;
GO
CREATE TRIGGER t6ins ON t6
    FOR INSERT
AS
    BEGIN
        INSERT  t7
                DEFAULT VALUES
    END ;
GO
--End of trigger definition

SELECT  id
FROM    t6 ;
--IDs empty.

SELECT  id
FROM    t7 ;
--ID is empty.

--Do the following in Session 1
INSERT  t6
        DEFAULT VALUES ;
SELECT  @@IDENTITY ;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT  SCOPE_IDENTITY() ;
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/

SELECT  IDENT_CURRENT('t7') ;
/* Returns 100, the value inserted into t7, that is in the trigger.*/

SELECT  IDENT_CURRENT('t6') ;
/* Returns 1, the value inserted into t6 four statements before this query.*/

-- Do the following in Session 2.
SELECT  @@IDENTITY ;
/* Returns NULL because there has been no INSERT action
up to this point in this session.*/

SELECT  SCOPE_IDENTITY() ;
/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT  IDENT_CURRENT('t7') ;
/* Returns 100, the last value inserted into t7.*/

Answer 3

From what I've read scope_identity() should be the right answer, however it looks like there is a bug in SQL 2005 and SQL 2008 that can come into play if your insert results in a parallel query plan.

Take a look at the following articles for more details:

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT - Retrieve Last Inserted Identity of Record

Article: Six reasons you should be nervous about parallelism

See section titled: 1. #328811, "SCOPE_IDENTITY() sometimes returns incorrect value"

Answer 4

In that case you need to write the table name, what happens if you decide to change the table name? You then also must not forget to update your code to reflect that. I always use SCOPE_IDENTITY unless I need the ID from the insert that happens in a trigger then I will use @@IDENTITY

Also the bigger difference is that IDENT_CURRENT will give you the identity from another process that did the insert (in other words last generated identity value from any user) so if you do an insert and then someone does an insert before you do a SELECT IDENT_CURRENT you will get that other person's identity value

See also 6 Different Ways To Get The Current Identity Value which has some code explaining what happens when you put triggers on the table

Answer 5

The theory says: To be aware of race conditions and to do not care about inserts inside triggers, you should be using SCOPE_IDENTITY() BUT ... there are know bugs on SCOPE_IDENTITY() (and @@IDENTITY) as is mentioned and linked on other anwsers. Here are the workarounds from Microsoft that takes into account this bugs.

Below the most relevant part from the article. It uses output insert's clause:

DECLARE @MyNewIdentityValues table(myidvalues int)
declare @A table (ID int primary key)
insert into @A values (1)
declare @B table (ID int primary key identity(1,1), B int not null)
insert into @B values (1)
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()

set statistics profile on
insert into _ddr_T
output inserted.ID into @MyNewIdentityValues
    select
            b.ID
        from @A a
            left join @B b on b.ID = 1
            left join @B b2 on b2.B = -1

            left join _ddr_T t on t.T = -1

        where not exists (select * from _ddr_T t2 where t2.ID = -1)
set statistics profile off

select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY(),
    [IDENT_CURRENT] = IDENT_CURRENT('_ddr_T')
select * from @MyNewIdentityValues
go

Answer 6

SELECT IDENT_CURRENT -- as you said will give you the table specific last inserted identity value. There are issues associated with this, one the user need to have permission to see the metadata otherwise it returns NULL and second you are hardcoding the name of the table , which will cause a problem in case the table name changes.

The best practice is to use Scope_Identity together with a variable ...Look the following example

 DECLARE @myFirstTableID INT

  DECLARE @mySecondTableID INT

  INSERT INTO MYFirstTable (....) VALUES (.....)

   SELECT @myFirstTableID =SCOPE_IDENTITY()


  INSERT INTO MYSecondTable () VALUES (.....)


   SELECT @mySecondTableID=SCOPE_IDENTITY()

Thus by making use of variable and scope_identity next to the insert statement of interest, you can make sure that you are getting the right identity from the right table. Enjoy