SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
Reference : Pinal Dave (https://blog.sqlauthority.com)
230 Comments. Leave new
Hy sir .. i making an LMS system , in this i want courses to show by semester wise through inner join query because the courses table is different and that table is connected to semester table through foreign key.
so for this i created two the first store procedure one procedure is below here.
ALTER proc [dbo].[Insert_Semister_Type]
@S_typeName varchar(50)
as
insert into Semister_Type (SemisterName) values (@S_typeName)
return
and the second is here..
ALTER proc [dbo].[A_Update_Courses_By_Semister_Wise_For_Examination]
as
SELECT 0 as Of_C_ID,’—- Please Select —-‘ as CourseName
FROM Offered_Courses_Sylabus INNER JOIN
Ad_Teacher_In_Oferd_Course ON Offered_Courses_Sylabus.Of_C_ID = Ad_Teacher_In_Oferd_Course.Of_C_ID INNER JOIN
Program_Sylabus ON Offered_Courses_Sylabus.P_S_ID = Program_Sylabus.P_S_ID INNER JOIN
Courses ON Program_Sylabus.Course_id = Courses.Course_id
union
SELECT Ad_Teacher_In_Oferd_Course.Of_C_ID, Courses.CourseName
FROM Offered_Courses_Sylabus INNER JOIN
Ad_Teacher_In_Oferd_Course ON Offered_Courses_Sylabus.Of_C_ID = Ad_Teacher_In_Oferd_Course.Of_C_ID INNER JOIN
Program_Sylabus ON Offered_Courses_Sylabus.P_S_ID = Program_Sylabus.P_S_ID INNER JOIN
Courses ON Program_Sylabus.Course_id = Courses.Course_id
— THIS SEMISTER ID WILL ALWAYS BE CHANGE THERE ACORDING TO CURRENT SEMISTER —
where Semister_ID=1
return
here i am giving the semester value by default , what i want , i want that, when i insert the first new semester by store procedure 1 so that value should automatically assign to that 2nd procedure in which i am giving the default value.. i knew only that ,that will be done by @@identity but i don’t know how to do it.. please help me i am waiting for your response . thank you
THANK YOU! Great article as usual. I like the way you write and keep it short, sweet and accurate. Gaz
Thanks Gaz
some time ago, looking in internet I found this workaround that is working for me, but need to be done each time a database is published to update the triggers.
edit all the insert triggers in a database that is published, and execute for update them.
the change should be distributed to all servers.
/**********************************************************/
BEGIN TRIGGER
/**********************************************************/
DECLARE @identity int
DECLARE @strsql varchar(128)
set @identity=@@IDENTITY
/**********************************************************/
TRIGGER
/**********************************************************/
set @strsql=’select identity(int,’+CAST(@identity as varchar(15)) +’,1) as id into #temp’
exec(@strsql)
if @@error 0
goto FAILURE
return
/**********************************************************/
END TRIGGER
/**********************************************************/
I understand that at the begin of the trigger ths identity is stored, the trigger does the operations needed for distribution or whatever else, and at the end restores the original identity vale.
Hi Pinal,
I need to know the difference between T SQL features on 2008 vs 2012 and CDC SQL SERVER 2008 R2 and CDC SQL SERVER 2012. Can you please any one help me out on this.
Thank you sir for your solution it works fine for me
Hello Sir
when a Identity value is generated than where last identity value saved.
thank you, this saved my bug… :)
Hello Sir,
Please I have a table (marks) with about 586065 rows; when you try to insert records of about 1,200 rows into the table (marks) it takes about 140 seconds to complete insertion.
When you try to select from the table (marks), it takes about 10 seconds to fetch records.
Is there a way to minimize the time taken to insert or search significantly?
I’m using stored procedure in SQL Server 2008 r2
below is the stored procedure:
[my_DB].[Marks_INSERT]
(
@cExam AS varchar(50) = null,
@studID AS varchar(50) = null,
@semID AS varchar(10) = null,
@examID AS varchar(5) = null,
@dateTaken AS smalldatetime = null,
@marks AS float = null
)
AS
INSERT INTO
[my_DB].[Marks]
(
[CExam],
[StudID],
[SemID],
[ExamID],
[DateTaken],
[Marks]
)
VALUES
(
@cExam,
@studID,
@semID,
@examID,
@dateTaken,
@marks
)
SELECT SCOPE_IDENTITY()
I always had a doubt about whether these functions were “user session” specific. Thanks to you sir, now my doubt is all gone.
A very crisp and to the point article. Thanks
i need without identity
SELECT *
FROM foo
WHERE ID = (SELECT MAX(ID) FROM foo)
Which of these is used by JDBC to return auto generated keys after doing insert?