The IDENT_CURRENT function returns the last IDENTITY value generated for any specific table or view on an identity column in any session and any scope.
This function is limited only to a specific table or view regardless of any session and any scope.
The value returned by IDENT_CURRENT function may be the value that you have just inserted in a table or it may be value inserted at the same time in that table by any user in another session or scope .
So, you can use this function when you need last identity value of any table regardless any session or scope.
SYNTAX
IDENT_CURRENT( 'table_or_view' )
table_or_view
Is the name of the table or view whose identity value is returned.
Lets look at an example of IDENT_CURRENT function in SQL.
To demonstrate this, Lets create a two sample tables which contains an identity column as shown below.
CREATE TABLE dbo.TBL_A ( ID INT IDENTITY(1,1) , Name VARCHAR(5) ) CREATE TABLE dbo.TBL_B ( ID INT IDENTITY(1,1) , Name VARCHAR(5) )
Now we have a table named TBL_A and TBL_B as shown below.
SELECT * FROM dbo.TBL_A SELECT * FROM dbo.TBL_B
Lets see the current identity value in these table using IDENT_CURRENT function.
SELECT IDENT_CURRENT('TBL_A') AS LAST_IdentityValue
SELECT IDENT_CURRENT('TBL_B') AS LAST_IdentityValue
As you can see that the current identity value returned by IDENT_CURRENT function for both tables is 1 that is the seed value of an identity column as no record has been inserted yet into tables.
Now we will insert some records into tables.
Lets insert two records in table TBL_A and three records in TBL_B.
INSERT INTO dbo.TBL_A
(NAME) VALUES ('A1')
INSERT INTO dbo.TBL_A
(NAME) VALUES ('A2')
INSERT INTO dbo.TBL_B
(NAME) VALUES ('B1')
INSERT INTO dbo.TBL_B
(NAME) VALUES ('B2')
INSERT INTO dbo.TBL_B
(NAME) VALUES ('B3')
Lets see the last identity value in tables using IDENT_CURRENT function.
SELECT IDENT_CURRENT('TBL_A') AS LAST_IdentityValue
SELECT IDENT_CURRENT('TBL_B') AS LAST_IdentityValue
As you can see this time, IDENT_CURRENT function returns the last identity value for table TBL_A that is 2 as you have just inserted two records in table that means the identity seed value is increased by 2 so that it returns last identity value as 2.
In similar way, the last identity value for TBL_B that is 3 as three records are inserted in this table so the identity seed value is increased by three so that the last identity value is 3.
Lets check the tables to see the last identity value on identity column.
SELECT * FROM dbo.TBL_A SELECT * FROM dbo.TBL_B
As we know IDENT_CURRENT function is limited only to a specific table or view regardless of any session and any scope.
If you check the last identity value for these tables in different different session, it will returns exactly the same values.
To see the same, just run the above scripts in another query editor window, this time we will also fetch the session id using the @@SPID.
SELECT @@SPID AS SESSION_ID -- Session Id of current user process
SELECT IDENT_CURRENT('TBL_A') AS LAST_IdentityValue
SELECT IDENT_CURRENT('TBL_B') AS LAST_IdentityValue
First we execute the script in current session, session id of current user process is 53, that you can see on the query editor tab or in query output.
As you can see, it returns last identity value for table TBL_A is 2 and for TBL_3 is 3.
Lets open another query editor window and execute the same scripts again, the purpose of doing so is to see the output of IDENT_CURRENT function in different session and you can see this time the session id is 59.
And you can also see that the last identity values for the tables is exactly same that means IDENT_CURRENT function is not session specific.
IDENT_CURRENT VS @@IDENTITY VS SCOPE_IDENTITYÂ
Suppose that you have a trigger created on table TBL_A, which inserts a record in table TBL_B when a record is inserted in table TBL_A.
CREATE TRIGGER Trg_TBL_A ON TBL_A FOR INSERT
AS
BEGIN
INSERT INTO dbo.TBL_B (NAME)
VALUES('B4')
END
Lets insert a record in table TBL_A, which will fire the trigger and one record will be inserted in table TBL_B as well.
INSERT INTO dbo.TBL_A
(NAME) VALUES ('A3')
Lets see the last identity values for TBL_A and TBL_B.
SELECT IDENT_CURRENT('TBL_A') AS LAST_IdentityValue
SELECT IDENT_CURRENT('TBL_B') AS LAST_IdentityValue
As you can see the last identity values for TBL_A is 3 and TBL_B that is 4, Value are increased by one after the previous identity values, That means IDENT_CURRENT function just returns the last identity value of a specific table regardless of the scope which generates the identity values.
To see the last identity value generated in any scope or current scope then you can use @@IDENTITY and SCOPE_IDENTITY function.
If you see the last identity value generated using SCOPE_IDENTITY function, it returns the last identity value generated in current scope, that is the value inserted to the table TBL_A using the first INSERT statement that insert record in table TBL_A.
SELECT SCOPE_IDENTITY() AS Identity_Value
As you can see SCOPE_IDENTITY returns the last identity value generated in the current scope only that means last identity value is that value which is generated by inserting a record into the TBL_A not the value which is inserted in table TBL_B by trigger.
Lets see the last identity value generated by @@IDENITITY function.
SELECT @@IDENTITY AS Identity_Value
As you can see the, last identity value returned by @@IDENTITY function is 4 that is the last identity value inserted in TBL_BÂ by trigger.
That means @@IDENTITY function returns the last generated identity value regardless of the scope that generates that value and this value is from insert statement which insert record in second table TBL_B when trigger fires.
As we have already seen that IDENT_CURRENT function is not a session specific. But @@Identity and SCOPE_IDENTITY function are session specific.
If you try to get last identity values generated for tables from another session both function will return null values.
Lets execute the below scripts in same session where you have executed the above insert statement ( inserted a record in TBL_A which fires the triggers).
You will see that @@identity and Scope_Identity function returns identity values, as it is session specific so that it will return the identity value only for session 53.
SELECT @@SPID AS SESSION_ID -- Session Id of current user process
SELECT IDENT_CURRENT('TBL_A') AS LAST_IdentityValue_Ident_Current
SELECT IDENT_CURRENT('TBL_B') AS LAST_IdentityValue_Ident_Current
SELECT SCOPE_IDENTITY() AS Identity_Value_@@Identity
SELECT @@IDENTITY AS Identity_Value_Scope_Identity
When you select last identity value from different session, you will see only the IDENT_CURRENT function returns the last identity value as it is not a session specific.
While @@IDENTITY function and SCOPE_IDENTITY function returns null as the identity value was generated in the session 53 only which means these functions are session specific.
Also Read..
