@@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! Cheers!
Republished from Sql&Me [31 clicks].
Read the original version here [32134 clicks].