A SQL Server IDENTITY column is used to generate key values automatically based on a provided seed and increment value.
IDENTITY [(seed, increment]
- Seed: is the value of the first row loaded into the table.
- Increment: is the incremental value added to the identity value of the previous row.
Download: SQL Server Identity functions script
Quick revision: SQL IDENTITY functions
- The @@identity function returns the last identity created in the same session.
- The scope_identity() function returns the last identity created in the same session and the same scope.
- The ident_current(name) returns the last identity created for a specific table or view in any session.
Session: The session is the database connection.
Scope: The scope can be a current query, stored procedure, function and trigger.
Now, let’s start with an example
1. Create two tables using IDENTITY seed and increment
CREATE TABLE employee_1 ( ID INT IDENTITY (1,1) PRIMARY KEY, empName nvarchar(50) ) CREATE TABLE employee_2 ( ID INT IDENTITY (10,1) PRIMARY KEY, empName nvarchar(50) )
- Now check current Identity value of above created two tables
SELECT IDENT_CURRENT('employee_1') AS employee_1_CurrentID GO SELECT IDENT_CURRENT('employee_2') AS employee_1_CurrentID GO
- Insert few records under both tables
INSERT INTO employee_1 (empName) VALUES ('Maxi'),('Alen'),('Rosh'),('Tom') INSERT INTO employee_2 (empName) VALUES ('Harry'),('Getten'),('Rock'),('Matt')
- Let’s check the current identity values again to using IDENT_CURRENT function
As expected, function returns the last identity created for a specific table or view in any session.
- Output with current session id: 59
- Output with other session id: 63
- Let’s check the value of last identity for @@IDENTITY function
As expected function returns the last identity created in the same session.
- Same Session Output
- Other Session Output, return null
- Let’s check the value of last identity for SCOPE_IDENTITY function
It is similar to @@IDENTITY function, returns the last identity created in the same session.
- Output in same session:
- Output in other session:
What is main difference between @@IDENTITY & SCOPE_IDENTITY() ?
If you have a trigger on the table and you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.
- Create trigger on First table
CREATE TRIGGER employee_1_Trigger ON employee_1 FOR INSERT AS BEGIN INSERT into employee_2(empName) values('Jerry') END
- Insert record into first table
INSERT into employee_1(empName) values('Wong Lee')
- Now execute both function together
SELECT @@IDENTITY AS Last_IDNT Go SELECT SCOPE_IDENTITY() AS ScopeID GO
- Final output
Hope you clear the difference between @@IDENTITY & SCOPE_IDENTITY().
Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.
Recommended SQL Server Post:
REPLICATE() function in SQL Server
SQL Server Configuration Functions
Query Optimization Technique in SQL Server