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...)

16 Comments

  • > @@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.

  • >It's usual (actually, I'd say recommended)



    In none of our applications. Ups.

  • >> 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...

  • 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.

  • Okay, whoops. I need to read more, the author does point out Scope_Identity is better to use. Sorry about that.

  • 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....

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

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

  • 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

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

  • First Sample is good one!

  • select max(identity column) from tablename

    example:

    create table employees
    (id int identity(1,1))

    select max(id) from employees

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

  • SELECT IDENT_CURRENT('Table_Name')

  • 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?

  • Good article on identify column.

Comments have been disabled for this content.