Use the right tool to get identity values back after an insert
Tags: sql, sqlserver
Published: 2019-10-03 09:25:46

This is a shortened version of [my last blog post](https://zoharpeled.wordpress.com/2019/10/03/use-the-right-tool-to-get-identity-values-back-after-an-insert/), but to compensate for the shortness, I've included the code samples directly here (An online runnable demo code is [available on rextester.](https://rextester.com/discussion/WQUA98482/Use-the-right-tool-to-get-identity-values-back-after-an-insert)).

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

* [@@Identity](https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-2017)
* [Scope_Identity()](https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017)
* [Ident_Current()](https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql?view=sql-server-2017)
* [Output](https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017)

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](https://zoharpeled.wordpress.com/2019/10/03/use-the-right-tool-to-get-identity-values-back-after-an-insert/). For all the details, read the official documentation.

# Let's see some code


First, lets create a couple of tables with identity columns:
```sql
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)
);
```

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

```sql
CREATE TRIGGER TblIdentityDemo_Insert 
ON TblIdentityDemo 
FOR INSERT
AS   
    INSERT INTO TblTriggeredWithIdentity 
    DEFAULT VALUES;
```

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

```sql
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;
```

Results:
```
demo					  Scope_Identity()  IDENT_CURRENT('TblIdentityDemo')	@@IDentity	Id	Col
After a succesful insert  2				    2									-10			2	2
```

Then, a failed insert (Col is the primary key, so no duplicate values allowed!)
```sql
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;
```

Results:
```
demo					Scope_Identity()	IDENT_CURRENT('TblIdentityDemo')	@@IDentity	Id	Col
After a failed insert	2					3									-10			2	2
```

And finnaly, a taste of the output clause:
```sql
INSERT INTO TblTriggeredWithIdentity (Col) 
OUTPUT 'output' as 'demo', Inserted.id, Inserted.Col 
VALUES (1), (2), (3), (4);
```


Results:
```
demo	id		Col
output	-20		1
output	-30		2
output	-40		3
output	-50		4
```