We understood that the identity column values are generated automatically. Sql Server has several methods to generate the last identity value that is generated. The most common way is to use SCOPE_IDENTITY() built into function.
In addition, using SCOPE_IDENTITY(), you also have the @@IDENTITY and IDENT_CURRENT('TableName') function.
Example query to get the last generated identity value
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('tblemployee')
Let's now understand the difference between, above 3 approaches
SCOPE_IDENTITY () returns the last identity value created in the same session (connection) and the same scope (same stored procedure, function, trigger). Suppose, I have 2 tables tblemployee1 and tblemployee2, and I have a trigger on the tblemployee1 table, which will insert a record into the tblemployee2 table. Now, when you insert a record into the tblPerson1 table, SCOPE_IDENTITY () returns the error value that is generated in the tblemployee1 table, where @@ as the identity returns, the value that is generated in the tblemployee2 table. So, @@ identity returns the last value that is created in a single session without any consideration. IDENT_CURRENT ('tblemployee') Returns the last identity value created for a specific table in any session and any region.
In summary:
SCOPE_IDENTITY () - The last identified value that is created in the same session and in the same scope.
@@ identity - The last identity value that is created in a single session and in any scope.
IDENT_CURRENT ('TableName') - Returns the last identity value created for a specific table in any session and any region.
