How to get an Identity value with SQL Server 2005

It's usual (actually, I'd say recommended) to have an Identity int column as primary key. I.e., it's a common thing to define a table like this:

CREATE TABLE HumanResources.Employees(

  Id int IDENTITY(1,1) NOT NULL,

  -- Other columns

,

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (Id ASC)

)

Now what is an interesting question is how to find out the Id of a newly inserted row; usually you write code like this:

DECLARE @NewId AS int

INSERT INTO HumanResources.Employees

  ( /* column names */)

  VALUES ( /* column values */)

SELECT @NewId = @@Identity

In this way we get the newly generated identity in @NewId . First of all *this code sample is wrong* Why? Because @@Identity returns the last identity value inserted in *any table or session of the database*, and so in a high (or even medium) concurrency environment @NewId will give you unexpected values. For this reason, it's a far better idea to use such functions as Scope_Identity() or Ident_Current().

Interestingly, SQL Server 2005 introduces the OUTPUT clause that allows us to find out the values (original or new) for columns manipulated by any INSERT, UPDATE or DELETE statement. With OUTPUT's help, the previous example can be rewritten like this:

DECLARE @InsertedRows AS TABLE (Id int)

DECLARE @NewId AS INT

INSERT INTO HumanResources.Employees

  ( /* column names */)

OUTPUT Inserted.Id INTO @InsertedRows

  VALUES (/* column values */)

SELECT @NewId = Id FROM @InsertedRows

Note the @InsertedRows temporary table declaration and the use of the OUTPUT clause right before the VALUES clause inside the INSERT statement. This code is not only concurrency-safe, but it allows us to get the values of other columns that could have been generated (e.g. by DEFAULTs or TRIGGERs). Moreover, as I already mentioned, it can also be used with UPDATEs and DELETEs which makes it useful for those who like to have record-level logs (even though I prefer to use business level logs, but that's another story...)

Published Monday, April 24, 2006 1:31 AM by Edgar Sánchez
Filed under:

Comments

# re: How to get an Identity value with SQL Server 2005

Monday, April 24, 2006 3:50 AM by Joe
> @@Identity returns the last identity value inserted in *any table or session of the database*

Any table, yes, but only the current session. It's not affected by concurrency, only by inserts into other tables, e.g. from a trigger.

IDENT_CURRENT is the one that returns values for any session.

Which leaves SCOPE_IDENTITY() is the usual solution.

# re: How to get an Identity value with SQL Server 2005

Monday, April 24, 2006 4:20 AM by Thomas Tomiczek
>It's usual (actually, I'd say recommended)

In none of our applications. Ups.

# re: How to get an Identity value with SQL Server 2005

Monday, April 24, 2006 8:10 AM by Merrion
>> It's usual (actually, I'd say recommended) to have an Identity int column as primary key

Only if (a) your data doesn't have a true key and (b) doesn't need to be globaly unique.

In many cases a true key does exist - i.e. all currencies have a unique 3 letter code, all cars have an unique chassis number and so on...

# re: How to get an Identity value with SQL Server 2005

Tuesday, April 25, 2006 7:11 AM by Mike Wood
And what about the Scope_Identity function? This limits down not only to the same connection, but also the same scope (stored procedure, trigger, function or batch).

I will have to admit that the output clause is interesting, but I think I'll stick with Scope_identity() to get my inserted identity fields.

# re: How to get an Identity value with SQL Server 2005

Tuesday, April 25, 2006 7:13 AM by Mike Wood
Okay, whoops. I need to read more, the author does point out Scope_Identity is better to use. Sorry about that.

# re: How to get an Identity value with SQL Server 2005

Monday, January 22, 2007 9:54 AM by Kjetil

Any idea on how to use this in conjunction with ADO.NET? Using the old "scope_identity()" and return value from stored procedure, one could simply map this to a SqlDataSource object using e.g. an InsertParameter with Direction="Output". But I don't see any way to accomplish this using the new output clause in SQL Server 2005....

# re: How to get an Identity value with SQL Server 2005

Monday, October 1, 2007 1:29 PM by Sam

How can i get the Identity for the last updated record?

# re: How to get an Identity value with SQL Server 2005

Friday, January 4, 2008 9:10 AM by rohit

@@Identity returns the last identity value inserted in *any table

# re: How to get an Identity value with SQL Server 2005

Thursday, May 29, 2008 2:23 PM by Kila

I just have to add that SCOPE_IDENTITY does NOT work with the uniqueidentifier data type, so OUTPUT is useful in that situation. SCOPE_IDENTITY only works for numeric columns

# re: How to get an Identity value with SQL Server 2005

Wednesday, October 8, 2008 12:16 PM by sabuv

i want to get the current identity value of a table before insert the next row.

# re: How to get an Identity value with SQL Server 2005

Friday, October 10, 2008 2:14 AM by Shantha

First Sample is good one!

# re: How to get an Identity value with SQL Server 2005

Tuesday, August 25, 2009 12:48 AM by sourabh

select max(identity column) from tablename

example:

create table employees

(id int identity(1,1))

select max(id) from employees

# re: How to get an Identity value with SQL Server 2005

Sunday, June 13, 2010 8:16 PM by kikus

отличный пост, автор пиши ещё

# re: How to get an Identity value with SQL Server 2005

Sunday, July 18, 2010 12:27 PM by Muhammad Abrar Ullah

SELECT IDENT_CURRENT('Table_Name')

# re: How to get an Identity value with SQL Server 2005

Tuesday, August 31, 2010 8:22 AM by shan

create table table1 (col1 int identity(1,5),col2 varchar(10))

alter table table1 modify col1 int identity(1,1)---------- gives error

how to alter the identity column?

# re: How to get an Identity value with SQL Server 2005

Friday, November 5, 2010 12:43 PM by hyderabad

Good article on identify column.