MS SQL Basics : scope_identity(), @@identity, IDENT_CURRENT

March 31, 2011 by Christoff Truter SQL  

Retrieving the last inserted identity(ID) on a table is a very common task when working with pretty much any relational database. There are a number of ways to do this, most of these "ways" are unfortunately potentially dangerous and must be avoided if possible.

Instead of simply giving you definitions on some of these methods, lets take a more practical approach in order to see the potential pitfalls (it also makes for a longer post at least, hehe).

Firstly we're going to create two tables (for now at least) for testing purposes:

CREATE TABLE [dbo].[x] (
[xID] [int] IDENTITY(1,1) NOT NULL,
[ID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_x] PRIMARY KEY CLUSTERED 
(
	[xID] ASC
))

GO

CREATE TABLE [dbo].[y] (
[yID] [int] IDENTITY(1,1) NOT NULL,
[xID] [int] NOT NULL,
[ID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_y] PRIMARY KEY CLUSTERED 
(
	[yID] ASC
))
GO

We're also going to use the following stored procedure to help us to do some "damage":

CREATE PROCEDURE [dbo].[Test]
AS
BEGIN
	DECLARE	@xID INT, @ID UNIQUEIDENTIFIER
			
	SET @ID = NEWID()
			
	INSERT INTO x(ID)
	VALUES(@ID)

	--SET @xID = -- Some Retrieval Method

	INSERT INTO y(xID, ID)
	VALUES(@xID, @ID)
END

And we'll use the following query to do a quick validation on our data:

select count(*) from x
join y on x.xID = y.xID
		and x.ID = y.ID

We're also going to write a little C# console app to simulate users using this stored procedure within an application:

using System.Data.SqlClient;
using System.Configuration;
using System.Threading;

class Program
{
    static void Main(string[] args)
    {
        for (int i = 0; i < 10; i++)
        {
            new Thread(() =>
            {
                using (SqlConnection connection = new SqlConnection("some connection"))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("Test", connection))
                    {
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                    }
                }
            }).Start();
        }
    }
}

The first function we're going to look at is @@identity - we alter the test stored procedure with the following:

SET @xID = @@IDENTITY

We run the console application, run the validation query, mmm 10 rows, seems like everything is in order... or is it? Things get interesting as soon as we add another table and a trigger to the equation:

CREATE TABLE [dbo].[z](
[zID] [int] IDENTITY(1,1) NOT NULL,
[xID] [int] NOT NULL,
[ID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_z] PRIMARY KEY CLUSTERED 
(
[zID] ASC
))

CREATE TRIGGER [dbo].[triggerHappy]
ON [dbo].[x]
AFTER INSERT
AS
INSERT INTO z(xID, ID)
SELECT xID, ID
FROM inserted

Okay, so after we amended our database we run the console app again and do validation, mmm 10 rows... uhhh that can't be right? Surely we've got 20 rows now? A look at the data reveals that everything is quite messed up! We do infact have 20 rows, but all the relationships are mucked up...

What happend here? Well, instead of inserting the last identity from table x, we're actually busy inserting identities from table z (thanks to the trigger) which basically gives us the clue that @@identity is not limited to the current scope (scope being our stored procedure), any new identity inserted regardless of table in our session will be returned - not ideal at all.

We can obviously use the IDENT_CURRENT function if we need to get a table specific identity, but unfortunately that also suffers from the same issues as @@identity - out of scope influences.

How do we get around all of these issues?

Well... This is where the scope_identity function (introduced in SQL 2000) comes into play.

As soon as you update the test stored procedure with this:

SET @xID = scope_identity()

You will notice that after you've run the console app, that everything will work like expected - scope_identity only returns the last inserted identity within the current scope - making this this our preferred method.