Developer Geeks Home

How to return last inserted IDENTITY column value in SQL Server

RSS
 

  • Posted 2 years ago
  • Category: SQL Server, SQL Server 2005,
  • Audiences: Architect, Developer, System Analyst,

This article will demonstrate different ways to return last inserted IDENTITY column value in SQL Server, with code samples.

Introduction

Let's briefly see what are the different ways to return last inserted IDENTITY column value in SQL Server:

I am going to introduce you to the following different functions in SQL Server, that could be used to perform this job:

@@IDENTITY - A system function that returns the last inserted identity value.

OUTPUT clause - Returns the last inserted identity column value of a table generated through new identity value, the computed column value or the default value from a default constraint.

SCOPE_IDENTITY - A function that returns the last identity value inserted into an identity column in the same scope, i.e. a stored procedure, trigger, function, or batch.

IDENT_CURRENT - A function that returns the last identity value generated for a specified table or view, for any session and any scope.

MAX - A function that returns the maximum value in the expression or table column.

Now, let's see in detail how these functions could be used with examples.

@@IDENTITY

@@IDENTITY returns the last identity value inserted in [any table in the database by any user/session].
Hence, this will return last inserted identity value from any table in the current database by any user/session.
Thus in a high or medium concurrency environment @@Identity value could be last identity value inserted by any user in any table of the database, and NOT the one you inserted.
Therefore, NEVER use @@Identity for returning last identity value inserted by you in your table.

For such a scenario your best bet would be to use Scope_Identity() or Ident_Current().

Example:

INSERT INTO Employees

  ( /* column names */)

  VALUES ( /* column values */)

DECLARE @NewId AS int

SELECT @NewId = @@IDENTITY

OUTPUT

SQL Server 2005 provided the OUTPUT clause that could be used to get values (original or new) from columns manipulated by any INSERT, UPDATE or DELETE statement.

Example:

DECLARE @InsertedRows AS TABLE (Id int)

DECLARE @NewId AS INT

INSERT INTO Employees

  ( /* column names */)

OUTPUT Inserted.Id INTO @InsertedRows

  VALUES (/* column values */)

SELECT @NewId = Id FROM @InsertedRows

The @InsertedRows temporary table declaration and the use of the OUTPUT clause right before the VALUES clause inside the INSERT statement.
This code is concurrency-safe, and it allows us to get the values of other columns that could have been inserted or modified by DEFAULTs or TRIGGERs.
Also, it could be used with UPDATE and DELETE statements.

SCOPE_IDENTITY()

SCOPE_IDENTITY() returns the last identity value inserted in the current scope, in your current session.
I.e., it will return the last identity value that you explicitly inserted in the current session. And NOT any other identity value that was inserted by a trigger or a user defined function.
Hence, this is the safest option in a high-concurrency environment to get the last identity value inserted by you in the current scope, in your current session.

Example:

INSERT INTO Employees

  ( /* column names */)

  VALUES ( /* column values */)

DECLARE @NewId INT;

SELECT @NewId = SCOPE_IDENTITY()

IDENT_CURRENT()

IDENT_CURRENT returns the identity value inserted in a specific table in any session and any scope.

Also, IDENT_CURRENT is limited to a specified table, regardless of the connection that created the value, and regardless of the scope of the statement that inserted the value.
The returned identity value could have also been inserted by DEFAULTs or TRIGGERs, and NOT by your statement.

Hence, this is not the safe option to get last inserted identity value in a high-concurrency environment.

Example:

INSERT INTO Employees

  ( /* column names */)

  VALUES ( /* column values */)

DECLARE @NewId INT;

SELECT @NewId = IDENT_CURRENT('Employees')

MAX()

Another scenario, where you want to return the last inserted value of an Id column which is NOT defined as an Identity column.
Here, you could use the MAX() function to get the maximum value of the Id column.
This code is also concurrency-safe.

Example:

INSERT INTO Employees

  ( /* column names */)

  VALUES ( /* column values */)

DECLARE @NewId INT;

SELECT @NewId = MAX(ID) FROM Employees
comments powered by Developer Geeks

My Recent Articles [86]