Notes on SQL

For several years I have happily relied on @@IDENTITY to return the latest identity of a row when inserting data. However, I have started using the OUTPUT clause and have discovered the entertainment to be had in getting the latest identity when writing to two or more tables with what can appear to be one command.

Although I’m sure most people are aware of @@IDENTITY, I’ll start with a basic example of that and build up from there.
I’ll create a very simple table, insert some data and check @@IDENTITY after each insert:

CREATE TABLE dbo.MainTable
(
    ID	INT IDENTITY(1,1) NOT NULL,
	TestText	VARCHAR(50) NULL
);
GO
INSERT INTO dbo.MainTable
		( TestText )
	VALUES ( 'Test 1' );

SELECT *
	FROM dbo.MainTable;

SELECT @@IDENTITY AS 'After First Insertion';

INSERT INTO dbo.MainTable
		( TestText )
	VALUES ( 'Test 2' );

SELECT *
	FROM dbo.MainTable;

SELECT @@IDENTITY AS 'After Second Insertion';

And the results are as I have always expected – @@Identity returns the value of the latest row ID after each of the two transactions.

The OUTPUT Clause

In some situations it is nice to be able to write the history of changes to another table. One way of doing this is to use the OUTPUT option within INSERT/UPDATE/DELETE and write the required information to another table.

Firstly, I’ll create an additional table for storing the history of updates to ‘MainTable’. I will deliberately start the Identity column from a different value, so it is apparent which identity we are looking at. In the real world the latest identity value of these two tables will differ anyway, because the history table has a row for each insert, update or deletion and so might have multiple rows that relate to one row in the ‘main’ table.

CREATE TABLE MainTable_History
(
    ID	INT IDENTITY(20,1) NOT NULL,
	MaintableID	INT NOT NULL,
	TestText	VARCHAR(50) NULL
);
GO

Now insert a third row into ‘MainTable’ and copy the data into ‘MainTable_History’.

INSERT INTO dbo.MainTable
		( TestText )
	OUTPUT Inserted.ID, inserted.TestText
		INTO	dbo.MainTable_History ( MaintableID, TestText )
	VALUES ( 'Test 3' );

SELECT *
	FROM dbo.MainTable;

SELECT *
	FROM dbo.MainTable_History;

SELECT @@IDENTITY AS 'After Third Insertion';

This time @@Identity has returned the identity of ‘MainTable_History’. Not what I was after.

Adding a Trigger

Now I’ll add a trigger to ‘MainTable’, which will write information to a new ‘Audit_Details’ table:

CREATE TABLE Audit_Details
(
    ID		INT IDENTITY (15,1),
	Audit_Action	VARCHAR(20)
)
GO
CREATE TRIGGER trgInsert ON MainTable FOR INSERT
AS
BEGIN
   INSERT dbo.Audit_Details
   		( Audit_Action )
   	VALUES ( 'TRIGGER TEST' )
END;
GO

Again, this additional table will have a different identity sequence.
Now insert another row into ‘Maintable’:

INSERT INTO dbo.MainTable
		( TestText )
		OUTPUT Inserted.ID,
		inserted.TestText
		INTO	dbo.MainTable_History(MaintableID,TestText)
	VALUES ( 'Test 4');

SELECT * FROM dbo.MainTable;
SELECT * FROM dbo.MainTable_History;
SELECT * FROM dbo.Audit_Details;

SELECT @@identity AS 'After Fourth Insertion';

@@Identity has returned the value of the ‘Audit_Details’ table, which was updated by the trigger – even further away from the result I was after.

Are there any other Identity Options?

Yes, there are two other commands for obtaining the latest identity value from a table.

SCOPE_IDENTITY returns the latest identity value within this ‘scope’ or batch. In this case it will return the latest identity from the Insert statement (which includes the OUTPUT clause), so will not return the value created by the trigger:

INSERT INTO dbo.MainTable
		( TestText )
		OUTPUT Inserted.ID,
		inserted.TestText
		INTO	dbo.MainTable_History(MaintableID,TestText)
	VALUES ( 'Test 5');

SELECT * FROM dbo.MainTable;
SELECT * FROM dbo.MainTable_History;
SELECT * FROM dbo.Audit_Details;

SELECT @@identity AS 'Identity After Fifth Insertion', SCOPE_IDENTITY() AS 'SCOPE_IDENTITY After Fifth Insertion';

In this example @@IDENTITY has returned the value created by the trigger but SCOPE_IDENTITY has returned the value raised by the INSERT/OUTPUT.
If we weren’t using the OUTPUT clause then SCOPE_IDENTITY would return the value from the ‘MainTable’ insert:

INSERT INTO dbo.MainTable
		( TestText )
	VALUES ( 'Test 6');

SELECT * FROM dbo.MainTable;
SELECT * FROM dbo.Audit_Details;

SELECT @@identity AS 'Identity After Sixth Insertion', SCOPE_IDENTITY() AS 'SCOPE_IDENTITY After Sixth Insertion';

IDENT_CURRENT() returns the latest identity value from a specified table, so it needs the table name as a parameter.

SELECT * FROM dbo.MainTable;
SELECT * FROM dbo.MainTable_History;
SELECT * FROM dbo.Audit_Details;

SELECT IDENT_CURRENT('MainTable') AS 'MainTable ID';
SELECT IDENT_CURRENT('MainTable_History') AS 'MainTable_History';
SELECT IDENT_CURRENT('Audit_Details') AS 'Audit_Details';

This looks like it is more useful; however it has no interest in scope or batches. It returns the latest value at the point the command was executed. So if another process is inserting data into the requested table then that value will be returned.

In one tab I’ll run a process that repeatedly inserts values into ‘MainTable’:

WHILE 1 = 1
INSERT INTO dbo.MainTable
		( TestText )
	VALUES ( 'Loop Test');

In another tab I’ll run several IDENT_CURRENT requests, separated by two seconds each:

SELECT IDENT_CURRENT('MainTable');

WAITFOR DELAY '00:00:02';

SELECT IDENT_CURRENT('MainTable');

WAITFOR DELAY '00:00:02';

SELECT IDENT_CURRENT('MainTable');

WAITFOR DELAY '00:00:02';

SELECT IDENT_CURRENT('MainTable');

So IDENT_CURRENT() just returns the latest identity value, regardless of what process is responsible for the update to that value.

So what about the OUPUT clause?

Unfortunately none of the three commands for obtaining the latest identity will return the required value when the OUTPUT clause is in use. For that I have to get the identity for the history table and (because I have stored it in the history table) retrieve the ‘MainTable’ ID from the appropriate column:

INSERT INTO dbo.MainTable
		( TestText )
		OUTPUT Inserted.ID,
		inserted.TestText
		INTO	dbo.MainTable_History(MaintableID,TestText)
	VALUES ( 'Test 7');

SELECT * FROM dbo.MainTable;
SELECT * FROM dbo.MainTable_History;

SELECT MaintableID AS 'The MainTable Identity' FROM dbo.MainTable_History WHERE ID = SCOPE_IDENTITY();

Conclusion

SCOPE_IDENTITY looks like a more useful command, instead of @@IDENTITY.
Triggers can cause issues when using @@IDENTITY.
The use of OUTPUT when updating data will cause further complications when using @@IDENTITY or SCOPE_IDENTITY.
IDENT_CURRENT() returns the latest ID from a table but doesn’t concern itself with which transaction is actually responsible for the update to the ID.

Further Reading:
https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql