From the previous session, we understood that identity column values are auto generated. There are several ways in sql server, to retrieve the last identity value that is generated. The most common way is to use SCOPE_IDENTITY() built in function.
Apart, from using SCOPE_IDENTITY(), you also have @@IDENTITY and IDENT_CURRENT('TableName') function.
Example queries for getting the last generated identity value
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('tblPerson')
Let's now understand the difference between, these 3 approaches.
SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same Stored procedure, function, trigger). Let's say, I have 2 tables tblPerson1 and tblPerson2, and I have a trigger on tblPerson1 table, which will insert a record into tblPerson2 table. Now, when you insert a record into tblPerson1 table, SCOPE_IDENTITY() returns the idetentity value that is generated in tblPerson1 table, where as @@IDENTITY returns, the value that is generated in tblPerson2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope. IDENT_CURRENT('tblPerson') returns the last identity value created for a specific table across any session and any scope.
In brief:
SCOPE_IDENTITY() - returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY - returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT('TableName') - returns the last identity value that is created for a specific table across any session and any scope.
Apart, from using SCOPE_IDENTITY(), you also have @@IDENTITY and IDENT_CURRENT('TableName') function.
Example queries for getting the last generated identity value
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('tblPerson')
Let's now understand the difference between, these 3 approaches.
SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same Stored procedure, function, trigger). Let's say, I have 2 tables tblPerson1 and tblPerson2, and I have a trigger on tblPerson1 table, which will insert a record into tblPerson2 table. Now, when you insert a record into tblPerson1 table, SCOPE_IDENTITY() returns the idetentity value that is generated in tblPerson1 table, where as @@IDENTITY returns, the value that is generated in tblPerson2 table. So, @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope. IDENT_CURRENT('tblPerson') returns the last identity value created for a specific table across any session and any scope.
In brief:
SCOPE_IDENTITY() - returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY - returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT('TableName') - returns the last identity value that is created for a specific table across any session and any scope.
Hello,
ReplyDeleteCan you please upload the Presentations of SQL Server. That would be really beneficial.
Thank You
IF WE USE ANY OF THE ABOVE MENTIONED THREE FUNCTION BEFORE THE INSERT STATEMENT WAHT WILL BE THE O/P
ReplyDeletethanks
wasim.add@gmail.com
https://www.youtube.com/watch?v=n1iwngG_zNY&list=PL_nMO-wncU0nYz_BFwHJENd2YWoobA9Ly&index=8
DeleteHello Wasim,
ReplyDeleteIf you execute SCOPE_IDENTITY() or @@IDENTITY for the first time in the current session before insert statement then you will get NULL.
When it comes to IDENT_CURRENT('TABLENAME'), the result is same if no data is inserted by any of the sessions.
Hello Sir.
ReplyDeleteWhen I insert record in table and get identity column that work fine.
But problem is that when exception in program like(Primary Key)
occurs then data not save in table but Identity column increment ?
(in my program Identity column is not primary key )
Hi Jits Look at the below code.. the answer for ur question..
ReplyDeleteWhen error occurs the Identity value is skipped ..
So if u don't want to skip the Identity value use the below code..
Here i have taken Email as Unique Key .. So when i insert same email id again i will get error number as 2627 for Unique key violation..
Begin Transaction
insert into tblPerson values('bobayya','hbc',2)
if(@@Error = 2627)
Begin
Declare @Id int
Set @Id = @@IDENTITY
DBCC CheckIdent('tblPerson',reseed, @Id)
end
Else
Begin
Commit Transaction
End
In the above code when error occurs ,, i m just setting the Id to current Identity value .. so when u insert the row next time the Id value is not skipped.. it will continue consecutively...
I can't understand what these functions used for?
ReplyDeletecan you please help me to understand
Scope stands for what purpose in this case ?
ReplyDeleteThank you so much sir and much love from Afghanistan these videos and slides are so beneficial.
ReplyDelete