Functions - @@IDENTITY, SCOPE IDENTITY, IDENT CURRENT

From SQLServerPedia

Jump to: navigation, search

When you insert a row into a table with an identity column, SQL generates the identity value and inserts it behind the scenes. There are several ways to retrieve that value:

  • SELECT
  • @@IDENTITY
  • SCOPE_IDENTITY
  • IDENT_CURRENT

One method isn’t universally better or worse than another. You just need to understand the behavior of each one, and make sure you get the intended results.

Contents

Select

A simple way to get the IDENTITY id of an inserted row is to search by a unique value, or to select the max id. 

SELECT IdentityCol, UniqueName 
FROM ThingTable 
WHERE UniqueName = 'One of a kind'

This will return the IdentityCol associated with the row having a UniqueName of 'One of a kind'. This technique counts on having a unique search value, which is not always the case. Plus, you're potentially searching on a large column, like varchar, when we could be working with smaller, more efficient data types (like int).

We can also select the maximum value from the identity column: 

SELECT MAX(IdentityCol) 
FROM ThingTable

Selecting the maximum identity column has another disadvantage: the max ID is not necessarily the one you inserted. If another row was inserted any time after yours - by a user, program, trigger, or anything else - you won't get the value you need.

Not to mention, SQL provides three really peachy functions for us!

@@IDENTITY

The @@IDENTITY function returns the last identity value inserted in any table in the current session. So if you insert a row in Table1, insert a row in Table2, and select @@IDENTITY, you’ll get the identity value inserted in Table2. 

create table table1 (col1 int identity(1,1), col2 varchar(100))
create table table2 (col1 int identity(20,1), col2 varchar(100))

INSERT table1 VALUES ('bork')
INSERT table2 VALUES ('borkBORK')

select @@IDENTITY	-- Returns 20

Let's consider a different scenario: If you insert a row in Table1, and another session inserts a row in Table1, you'll get the identity value for the row you inserted (not the most recent row inserted).

SCOPE_IDENTITY

SCOPE_IDENTITY is named well: it returns the last identity value generated for any table, not just in the current session, but also for the current scope. That may be a tad confusing, so let’s talk about sessions and scope.

A session is a given database connection. One window in Query Analyzer is a session; so is one job executing, one website connection, and so on. We could say that a session is defined by one SPID.

Scope always refers to the context in which an object is valid. Here, moving to or from a stored procedure, trigger, or batch is a change in scope.

Example time. Let’s create a table, and a procedure to insert rows into that table: 

create table table1 (col1 int identity(1,1), col2 varchar(100))
GO

CREATE PROC InsertRow1 @value varchar(100)
AS
  INSERT table1 VALUES (@value)
  RETURN 0
GO

When we insert a row (or rows), SCOPE_IDENTITY() returns the identity value of the last row inserted: 

-- Insert row 1 
INSERT table1 VALUES ('bork')

select @@IDENTITY			[@@Identity], 	-- Returns 1
	SCOPE_IDENTITY()		[SCOPE_IDENTITY] 	-- Returns 1
  FROM table1

But when we run the procedure to insert a row, it’s in a different scope. We get SCOPE_IDENTITY() from the Query Analyzer window, which (in scope-speak) is the parent of the procedure. 

-- Run procedure to insert row 2
exec InsertRow1 'borkbork'

select @@IDENTITY			[@@Identity], 	-- Returns 2
	SCOPE_IDENTITY()		[SCOPE_IDENTITY] 	-- Returns 1!
  FROM table1

Fun stuff, that!

IDENT_CURRENT

IDENT_CURRENT returns the identity inserted in a specified table, without any scope or session limits. So if anyone anywhere inserts a row to Table1, IDENT_CURRENT() will return the most recently inserted identity value for that table.

References

See Also: Main_Page - Transact SQL Coding Techniques

Author Credits

Jen McCown

This wiki article was written by Jennifer McCown.

Jen McCown is a SQL Server developer and DBA with over 10 years experience. She writes book and product reviews for ITBookworm.com, makes training videos and blogs for MidnightDBA.com, and maintains both websites.

Her online presences include:

Retrieved from "http://sqlserverpedia.com/wiki/Functions_-_@@IDENTITY,_SCOPE_IDENTITY,_IDENT_CURRENT"