Identities in SQL
February 25, 2023 2023-02-26 19:58Identities in SQL
Subhajit SQL Identities are used to get the last identity column value.
The three identities in SQL are a. Scope_Identity(), b. @@Identity(), c. Ident_Current(TableName)
Above is the Employee table.
Above is the Test3 table
Now in the Employee table we will be creating a trigger as follows
CREATE TRIGGER [dbo].[TR_EMPLOYEE]
ON [dbo].[EMPLOYEE]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Test-3] VALUES ('UUUU')
END
So according to the above trigger, if we insert any record in the Employee table, then a new record will also be inserted in the Test3 table
Lets insert a row in the Employee table
INSERT INTO [dbo].[EMPLOYEE] ([FirstName],[LastName],[Age],[Country],[Salary],[ManagerId])
VALUES('Steve','Wozniac',28,'US' ,2500000 ,3)
Now a new row will be created in the Employee table as follows
Since an insert trigger has been created on the Employee table, a new row will also be created in the Test-3 table as follows
Lets write two insert queries to insert data into Em
Scope_Identity()
Scope_Identity() will retrieve the last generated identity from Same session and same Scope.
So here it will retrieve 1008 as the last generated identity value from the Employee table as follows
SELECT SCOPE_IDENTITY() as SCOPE_IDENTITY
@@Identity
@@Identity will retrieve the last generated identity from same session and across any scope.
So here it will retrieve 3 from the Test-3 table as it in different scope.
SELECT @@IDENTITY as IdentityColumn
Ident_Current(TableName)
Ident_Current(TableName) will retrieve the last generated identity from any session and any scope for a particular table.
Lets create two insert statements to insert Records in Employee tables in two different SQL session as follows
Now in session 1 lets call all the identities as follows:
The Employee Table will be as follows
The Test-3 table will be as follows
For Scope_Identity() we will get 1008 as the last inserted row id for Employee table in Session-1 is 1008
For @@Identity we will get 3 as the last generated row id, since the last inserted row id for the Test-3 table is 3
For Ident_Current we will get 1009 as the last generated row id, as the last inserted row id for the Employee table is 1009, which got generated in the Employee table when we ran the insert statement in Session-2, as Ident_Current retrieves last generated identity for any session and any scope for a specific table as follows
Please comment and share if you like this post and tell us about how we can enhance our posts in dotnetcoaching. Thanks.
