Another Way to Get Identity from SQL Server

• Download source - 919 B

Introduction

Recently, I tried to get the newly inserted Identity value from SQL Server using @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT. By using these three options, I was not able to get the expected output. Here, I am trying to explain the different way we can get the identity value from SQL Server, and how it’s failed to produce my expected result. Finally, I am going to explain the best option to get the accurate identity value for current scenario.

Background

We have different options to get the Identity values but all options will not give the same/expected result. By knowing all the options, we can take the better decision.

@@IDENTITY

It returns the last identity value generated by any table any scope in connection. This value is either created by user or automatic trigger.

SELECT @@IDENTITY

IDENT_CURRENT(‘table name’)

It returns the last identity value generated by specific table. It does not depend on any scope or session.

SELECT IDENT_CURRENT('table name')

SCOPE_IDENTITY()

It returns the last identity value generated by any table within the current scope and current session. This will not take values from automatic trigger.

SELECT SCOPE_IDENTITY()

OUTPUT CLAUSE

Output clause returns the information from each row affected by Insert statement which includes Identity.

DECLARE @IdentityTableVariable TABLE (id INT)
INSERT INTO OutputTable OUTPUT inserted.id INTO @IdentityTableVariable
SELECT tbl1.ReferenceValue, tbl2.InputValue FROM ReferenceTable

How @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT() Failed

In the current scenario, I have a while loop which will get the value from Table1 and Table2 using INNER JOIN and insert a new record into Table3. Table3 also has an AFTER INSERT TRIGGER. Based on the Identity values, I will insert the records into Table4 and Table5 which is Foreign Key constraint with Table3. If I am not getting any Identity, then I should not try to insert any values into Table3 and Table4.

Sample

CREATE TABLE ReferenceTable (id INT NOT NULL PRIMARY KEY, ReferenceValue VARCHAR(50));
CREATE TABLE InputTable (id INT NOT NULL PRIMARY KEY, InputValue VARCHAR(50));
CREATE TABLE OutputTable (id INT NOT NULL identity, ReferenceValue VARCHAR(50), InputValue VARCHAR(50));

CREATE TRIGGER InsertRecordsToOutputTable ON OutputTable
AFTER INSERT
AS
BEGIN
INSERT INTO OutputTable VALUES ('InsertedByTrigger', 'InsertedByTrigger')
INSERT INTO OutputTable VALUES ('InsertedByTrigger1', 'InsertedByTrigger1')
END

INSERT INTO ReferenceTable VALUES (1, 'Record1');
INSERT INTO ReferenceTable VALUES (2, 'Record2');
INSERT INTO ReferenceTable VALUES (3, 'Record3');
INSERT INTO ReferenceTable VALUES (4, 'Record4');
INSERT INTO ReferenceTable VALUES (5, 'Record5');
INSERT INTO InputTable VALUES (1, 'Record1');
INSERT INTO InputTable VALUES (2, 'Record2');
INSERT INTO InputTable VALUES (3, 'Record3');
INSERT INTO InputTable VALUES (6, 'Record6');
INSERT INTO InputTable VALUES (5, 'Record5');

DECLARE @TotalRecord INT
DECLARE @CurrentRecord INT = 0;
SELECT @TotalRecord = count(1)FROM ReferenceTable

DECLARE @IdentityTableVariable TABLE (id INT)

WHILE (@CurrentRecord <= @TotalRecord)
BEGIN
      DELETE FROM @IdentityTableVariable
     SET @CurrentRecord = @CurrentRecord + 1;
         DECLARE @tempValue INT = NULL
         INSERT INTO OutputTable OUTPUT inserted.id
         INTO @IdentityTableVariable
         SELECT tbl1.ReferenceValue, tbl2.InputValue FROM ReferenceTable tbl1
         INNER JOIN InputTable tbl2 ON tbl1.id = tbl2.id WHERE tbl1.id = @CurrentRecord

SELECT tbl1.ReferenceValue, tbl2.InputValueFROM ReferenceTable tbl1
         INNER JOIN InputTable tbl2 ON tbl1.id = tbl2.id WHERE tbl1.id = @CurrentRecord
SELECT * FROM OutputTable WHERE id = @@IDENTITY
SELECT * FROM OutputTable WHERE id = SCOPE_IDENTITY()
SELECT ( SELECT id        FROM @IdentityTableVariable ) AS OutputClause, 
                 SCOPE_IDENTITY() AS ScopeIdentity, 
                 IDENT_CURRENT('OutputTable') AS IdentCurrent, 
                 @@IDENTITY AS AtIdentity
END

Output

Option 1: @@IDENTITY

This failed because it includes identity value generated from Trigger.

Option 2: IDENT_CURRENT(‘table name’)

This one also failed for the same reason as above, it returns the Trigger generated value.

Option 3: SCOPE_IDENTITY()

Initially, this option seems to be working but when my INNER JOIN failed to produce a row, this option also failed. Because the expected result is NULL but SCOPE_IDENTITY() returns the previous loop identity value.

Option 4: OUTPUT CLAUSE

After doing some research, I found a new option which is OUTPUT CLAUSE.

This helps to get the expected result, i.e., when there is no insert, then return value is NULL.

Conclusion

As per Microsoft (https://support.microsoft.com/en-us/kb/2019779 ), @@IDENTITY and SCOPE_IDENTITY() may produce incorrect result in SQL Server 2005 and 2008, but this is not the reason for the current scenario.

By knowing different options to get the identity values from SQL Server, we can take better decision. In my case, the OUTPUT Clause is the most appropriate option.

History

• 26^th August, 2015: Initial version