SQL Server @@IDENTITY function is a system function that is used to get the last IDENTITY value generated for any table with an identity column under the current session, regardless of the scope of the T-SQL statement that generated the value.
Suppose you are generating multiple rows using BULK COPY, INSERT or SELECT INTO statement, then @@IDENTITY function will return the last identity value generated at the end of the last statement.
If the statement did not affect any tables with identity columns, the @@IDENTITY function returns NULL.
SYNTAX
@@identity
It returns a numeric value of size numeric (38,0)
Lets look at an example of @@identity in SQL Server.
To demonstrate this, first we create a two tables and insert some data into tables.
CREATE TABLE SALESMASTER(SNO INT IDENTITY(1,1), PRODUCTS VARCHAR(30)) INSERT INTO SALESMASTER (PRODUCTS) values ('Spare parts X-10'), ('Spare parts XII-16')
Now, create a second table.
CREATE TABLE SALESDETAILS(SNO INT IDENTITY(1,1), PURCHASEDDATE DATE) INSERT INTO SALESDETAILS (PURCHASEDDATE) VALUES ('2018/01/01')
As you can see, SalesMaster table are having two records and the value of last identity that is inserted in column SNO is 2 .
For table Salesdetails there are one record only and identity value for column is 1.
Now we will insert one record in table SalesMaster and see the result returned by @@identity function.
Suppose you are inserting a record in a table in current session – 53, so @@identity function will return a last identity value inserted in table for the current session.
As you can see, after inserting a one record in table, @@identity function returns last identity value that is 3, as it is a session specific, so it returns last identity value generated by last statement in current session.
If you try to get last identity value in another session, the @@identity function returns a null value, as in another session you did not insert any value in table.
Lets open another session window and select last identity value using @@IDENTITY function.
SELET @@IDENTITY AS checkidentity_session
As you can see, it returns null. It means @@function is a session specific and returns only last identity value inserted in current session.
@@IDENTITY function using multiple tables
As you can see, table SalesMaster contains three records and last identity column value is 3 while in table SalesDetails there are one record and last value for identity column is 1.
Now we will insert one records into each tables subsequently and then will see the last identity value using @@identity function.
As you can see, it returns 2 which is the last identity value inserted into SalesDetails table, which was generated at the end of the last statement.
Also Read..
