@@IDENTITY & SCOPE_IDENTITY & IDENT_CURRENT

 

@@IDENTITY : Fethch the last identity value of any current session.After insert , insert into , bulk insert , identity value is generated.If insertion sucessfully effect then , @@IDENTITY return the last value , if insertion do not effect then @@IDENTITY return null .Please remember that @@IDENTITY have to put immediately after the insertion statement.

CREATE TABLE student

(

id int identity,

student_name varchar(500)

)

 

We have just created table , not insertde data , I am executing @@IDENTITY

SELECT @@IDENTITY

it will retun --NULL

Now i am inserting data

INSERT INTO student(student_name)

SELECT 'student1'

UNION

SELECT  'student2'

Now i am executing the command

SELECT @@IDENTITY

it will retun --2, the last identity value

Now sometimes we need to reset the identity identity column.

we can simple run truncate statement to reset identity column.

TRUNCATE TABLE student

It will reset their column from the first

Another way is

DBCC CHECKIDENT('student', RESEED, 0)

The DBCC (Database Console Commands) command will reset the identity 

SCOPE_IDENTITY : Fetch the last identity value by current scope.There can be several type of Scope ,procedure , trigger , batch statement.To test the information i am creating a procedure , which insert value to student

CREATE PROCEDURE sp_student

(

  @return_id int out

)

AS

BEGIN

   INSERT INTO student(student_name)

   SELECT  'student1'

   UNION

   SELECT 'student2'

SET @return_id = scope_Identity()

END

Now i am executing the procedure

DECLARE @id INT

EXEC sp_student  @id output

SELECT @id

Procedure retuns-2

 

IDENT_CURRENT--Fetch the last identity value of a table.It can be of any session or any scope.For example if i insert from scope A , then from Scope B.

IDENT_CURRENT will return the last value inserted from Scope B

In above example i have created a procedure named 'sp_student' , i am calling again and inserting some manual data.

--Step 1  (2 data inserted)          

DECLARE @id INT

EXEC sp_student  @id OUTPUT

SELECT @id

--Step 2  (2 data inserted)

INSERT INTO student(student_name)

SELECT 'student3'

UNION

SELECT 'student4'

--Now ,if we run

SELECT IDENT_CURRENT('student')

result is 8