DEV Community

Zohar Peled
Zohar Peled

Posted on

Use the right tool to get identity values back after an insert

#sql #sqlserver

This is a shortened version of my last blog post, but to compensate for the shortness, I've included the code samples directly here (An online runnable demo code is available on rextester.).

SQL Server provides four ways to retrieve the newly generated identity value after rows have been inserted into a table:

All of these ways have their use cases, and there are subtle differences between each of them, that are important to understand.

So what are the differences, and which one should we use?

@@Identity, Scope_Identity(), and Ident_Current() are all similar functions because they return values that are inserted into identity columns. Output is not a function, but a clause we add directly into the DML statement we use.

The @@Identity function will return the last identity value inserted in the current session, in any table and in any scope.

The Scope_Identity() function will return the last identity value inserted in the current scope (and session), in any table.

The Ident_Current() function takes in a table (or view) name and returns the last identity value generated for that table, regardless of session or scope.

The output clause returns the values inserted directly from the DML statement it's a part of.

For more details, feel free to read my blog post. For all the details, read the official documentation.

Let's see some code

First, lets create a couple of tables with identity columns:

CREATE TABLE TblIdentityDemo
(
    id int identity(1,1),
    col int NOT NULL CONSTRAINT PK_TblIdentityDemo PRIMARY KEY
);

CREATE TABLE TblTriggeredWithIdentity
(
    id int identity(-10,-10),
    col int CONSTRAINT DF_TblTriggeredWithIdentity_Col DEFAULT(0)
);
Enter fullscreen mode Exit fullscreen mode

Then, create an after insert trigger on one table, that will insert records to the other table.

CREATE TRIGGER TblIdentityDemo_Insert 
ON TblIdentityDemo 
FOR INSERT
AS   
    INSERT INTO TblTriggeredWithIdentity 
    DEFAULT VALUES;
Enter fullscreen mode Exit fullscreen mode

And now - let's play: First, insert a couple of records into the first table:

INSERT INTO TblIdentityDemo (Col) 
VALUES (1), (2);

-- See what happens:

SELECT  TOP 1 
        'After a succesful insert' As 'demo',
        SCOPE_IDENTITY() As [Scope_Identity()], 
        IDENT_CURRENT('TblIdentityDemo') As [IDENT_CURRENT('TblIdentityDemo')], 
        @@IDentity As [@@IDentity],
        Id,
        Col
FROM TblIdentityDemo
ORDER BY Id DESC;
Enter fullscreen mode Exit fullscreen mode

Results:

demo                      Scope_Identity()  IDENT_CURRENT('TblIdentityDemo')    @@IDentity  Id  Col
After a succesful insert  2                 2                                   -10         2   2
Enter fullscreen mode Exit fullscreen mode

Then, a failed insert (Col is the primary key, so no duplicate values allowed!)

BEGIN TRY
    INSERT INTO TblIdentityDemo (Col) 
    VALUES (1), (2);
END TRY
BEGIN CATCH
  -- Do nothing
END CATCH

-- Now see what happens:

SELECT  TOP 1
        'After a failed insert' As 'demo',
        SCOPE_IDENTITY() As [Scope_Identity()], 
        IDENT_CURRENT('TblIdentityDemo') As [IDENT_CURRENT('TblIdentityDemo')], 
        @@IDentity As [@@IDentity],
        Id,
        Col
FROM TblIdentityDemo
ORDER BY Id DESC;
Enter fullscreen mode Exit fullscreen mode

Results:

demo                    Scope_Identity()    IDENT_CURRENT('TblIdentityDemo')    @@IDentity  Id  Col
After a failed insert   2                   3                                   -10         2   2
Enter fullscreen mode Exit fullscreen mode

And finnaly, a taste of the output clause:

INSERT INTO TblTriggeredWithIdentity (Col) 
OUTPUT 'output' as 'demo', Inserted.id, Inserted.Col 
VALUES (1), (2), (3), (4);
Enter fullscreen mode Exit fullscreen mode

Results:

demo    id      Col
output  -20     1
output  -30     2
output  -40     3
output  -50     4
Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
zchtodd profile image
zchtodd •

Nice! Although I have to admit I've never seen an identity column type before, what does that do? I've never used SQL Server before, so apologies if that's a super obvious question.

Collapse
 
peledzohar profile image
Zohar Peled •

Identity is not a column type but a property you can set on an integer column. (only when creating the column, though).

Once set, SQL Server will automatically populate the identity column with auto-generated values any time you insert rows to the table.

Basically, it's like a serial column in PostgreSQL or an auto_increment column in Oracle / MySql.

Read next

grahamthedev profile image

True Alphanumeric / natural sorting in MySQL - why is the answer always recursion?

GrahamTheDev -

mohamed_el_laithy profile image

How Pipe Syntax Fix SQL Design Problems

Mohamed El Laithy -

giulianaolmos profile image

Technical Interview Questions - Part 4 - Git + SQL vs noSQL

Giuliana Olmos -

dm8ry profile image

Populating a Vertica Database with Random Data

Dmitry Romanoff -