In this article, we will learn when and how to use @@IDENTITY, IDENT_CURRENT and scope_identity in sql server statements in sql server.
@@IDENTITY returns last identity value generated by the statement. If the statement having tables without identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, @@IDENTITY returns the last identity value generated.
For example if we create a country table and insert single row it will returns it CountryId as follows.
CREATE table dbo.Country(CountryId int identity Primary Key, CountryName varchar(200))
Go
INSERT INTO dbo.Country Values('India')
SELECT @@IDENTITY AS CountryId
Go
OUTPUT: (1 row affected) CountryId --------------------------------------- 1 (1 row affected)
But What will it returns in case of bulk insert ?
INSERT INTO dbo.Country Values('Australia'),('Russia'),('China')
SELECT @@IDENTITY AS CountryId
Go
OUTPUT: (3 rows affected) CountryId --------------------------------------- 4 (1 row affected)
It will returns last inserted row column CountryId i.e. 4
Lets take one more example with triggers
Create a new table dbo.CountryInfo
CREATE TABLE dbo.CountryInfo(CountryInfoId int identity primary key, CountryId int references dbo.Country, CountryCode varchar(3))
Go
Create Trigger after Insert for dbo.Country table, which will insert data in dbo.CountryInfo from it as follows.
CREATE TRIGGER dbo.trg_country ON dbo.Country
AFTER INSERT
AS BEGIN
INSERT INTO dbo.CountryInfo(CountryId,CountryCode) SELECT CountryId,SUBSTRING(CountryName,1,3) FROM inserted
END
Go
Lets check data in both table before insert
SELECT * FROM dbo.Country
SELECT * FROM dbo.CountryInfo
OUTPUT: CountryId CountryName ----------- --------------- 1 India 2 Australia 3 Russia 4 China (4 rows affected) CountryInfoId CountryId CountryCode ------------- ----------- ----------- (0 rows affected)
Insert one value in dbo.country
INSERT INTO dbo.Country Values('Uk')
SELECT @@IDENTITY AS CountryId
Go
OUTPUT: (1 row affected) (1 row affected) CountryId ----------- 1 (1 row affected)
Above insert statement will returns 1 instead of 5.
Because 1 is the last instead identity of dbo.CountryInfo by the trigger as shown below.
SELECT * FROM dbo.Country
SELECT * FROM dbo.CountryInfo
OUTPUT: CountryId CountryName ----------- ------------- 1 India 2 Australia 3 Russia 4 China 5 Uk (5 rows affected) CountryInfoId CountryId CountryCode ------------- ----------- ----------- 1 5 Uk (1 row affected)
So how we can get exact identity value of table with in scope if any trigger is present.
SCOPE_IDENTITY(): SCOPE_IDENTITY() is an system method, which returns last identity value inserted into an identity column of an table with in the same scope.
Lets insert one more value in dbo.country table returning SCOPE_IDENTITY() value as follows.
INSERT INTO dbo.Country Values('Canada')
SELECT SCOPE_IDENTITY() AS CountryId
Go
OUTPUT: (1 row affected) (1 row affected) CountryId ----------------- 6 (1 row affected)
It will returns exact IDENTITY value of dbo.country table i.e 6.
Lets view data in both the tables.
SELECT * FROM dbo.Country
SELECT * FROM dbo.CountryInfo
OUTPUT: CountryId CountryName ----------- ----------------- 1 India 2 Australia 3 Russia 4 China 5 Uk 6 Canada (6 rows affected) CountryInfoId CountryId CountryCode ------------- ----------- ----------- 1 5 Uk 2 6 Can (2 rows affected)
Scope: Scope is a module that can be a stored procedure or trigger or function or batch.
Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.
Here dbo.Country not in the scope of trigger. Hence SCOPE_IDENTITY() returns last inserted identity of it.
IDENT_CURRENT(): IDENT_CURRENT() is an system method, which returns the last identity value generated for a specified table or view.
Which can be for any session and any scope.
Syntax:
IDENT_CURRENT('table_name')
Lets Insert one more value for table dbo.Country
INSERT INTO dbo.Country Values('England')
SELECT IDENT_CURRENT('dbo.Country') AS CountryId
Go
OUTPUT: (1 row affected) (1 row affected) CountryId --------------------------------------- 7 (1 row affected)
IDENT_CURRENT() method returns 7 which is last generated identity of table dbo.Country
SELECT * FROM dbo.Country
SELECT * FROM dbo.CountryInfo
OUTPUT: CountryId CountryName ----------- ------------------ 1 India 2 Australia 3 Russia 4 China 5 Uk 6 China 7 England (7 rows affected) CountryInfoId CountryId CountryCode ------------- ----------- ----------- 1 5 Uk 2 6 Chi 3 7 Eng (3 rows affected)
Lets Getting identity for both the tables.
INSERT INTO dbo.Country Values('Egypt')
SELECT IDENT_CURRENT('dbo.Country') AS CountryId,IDENT_CURRENT('dbo.CountryInfo') AS CountryInfoId
Go
OUTPUT: (1 row affected) (1 row affected) CountryId CountryInfoId --------------------------------------- --------------------------------------- 8 4 (1 row affected)
Showing Data in both tables
SELECT * FROM dbo.Country
SELECT * FROM dbo.CountryInfo
OUTPUT: CountryId CountryName ----------- ---------------- 1 India 2 Australia 3 Russia 4 China 5 Uk 6 China 7 England 8 Egypt (8 rows affected) CountryInfoId CountryId CountryCode ------------- ----------- ----------- 1 5 Uk 2 6 Chi 3 7 Eng 4 8 Egy (4 rows affected)
Moral:
IDENT_CURRENT() Method is much better than SCOPE_IDENTITY() and SCOPE_IDENTITY() is better than @@IDENTITY.
Dropping all database sample structure
DROP TRIGGER dbo.trg_country
Go
DROP TABLE dbo.CountryInfo
Go
DROP TABLE dbo.Country
Go
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.