Posts Tagged ‘IDENTITY’

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

01.06.2011 No comments

@@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 (


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 (


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




(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!


Using Built-in system function – IDENTITY()

27.05.2011 No comments

IDENTITY() function can be used to create a new Identity column for a table, You can only use this function in a SELECT statement with INTO clause.


IDENTITY ( data type, seed, increment ) AS Column Name

for example,

I have the following table with two columns – Department Name and Manager ID:

SELECT      *

FROM        dbo.Departments

Result Set:

deptname                  deptmgrid

————————- ———–

HR                        2

Marketing                 7

Finance                   8

R&D                       9

Training                  4

Gardening                 NULL


(6 row(s) affected)

Now, if I need to add a new identity column to this table, I can do it using IDENTITY function.

An identity column can be added to above data using:

SELECT      IDENTITY(INT, 100, 2) AS deptid,



INTO        dbo.NewDepartments

FROM        dbo.Departments

Result Set:

(6 row(s) affected)

A new table dbo.NewDepartments is now created with an identity column – deptid.

SELECT      *

FROM        dbo.NewDepartments

Result Set:

deptid      deptname                  deptmgrid

———– ————————- ———–

100         HR                        2

102         Marketing                 7

104         Finance                   8

106         R&D                       9

108         Training                  4

110         Gardening                 NULL


(6 row(s) affected)

Hope This Helps!