Archive

Posts Tagged ‘SCOPE_IDENTITY’

Using Built-in system functions – @@IDENTITY, SCOPE_IDENTITY()

June 1, 2011 Leave a comment

@@IDENTITY is a system function that can be used to retrieve last inserted identity value. @@IDENTITY is not limited to scope.

for example, I have a Table which contains an identity column as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

USE [SqlAndMe]

 

CREATE TABLE [dbo].[Temp](

      [IDNum]     INT IDENTITY(100,1) NOT NULL,

      [Name]      NCHAR(10) NULL

)

After I insert new row to table, I can use this functions to retrieve the inserted identity value.

— © 2011 – Vishal (http://SqlAndMe.com)

 

INSERT INTO dbo.Temp VALUES ('Ahmedabad')

INSERT INTO dbo.Temp VALUES ('Bangalore')

INSERT INTO dbo.Temp VALUES ('Reading')

 

SELECT @@IDENTITY AS 'Last Inserted'

Result Set:

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

Last Inserted

—————————————

186

 

(1 row(s) affected)

@@IDENTITY only returns the last identity values inserted in case of multiple inserts.

 

SCOPE_IDENTITY() as the name suggests is limited to the same scope in which last identity value was inserted to a column. SCOPE_IDENTITY() should be used if you need to retrieve the last inserted value from the table you inserted the rows explicitly.

Another useful function for this task is IDENT_CURRENT().

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe