Obtaining the Last Inserted Identity in Any Session

Posted: 12 Şubat 2013 in SQL, SQL SERVER

It is a common task to obtain the last inserted identity value for a SQL Server table for the current connection, with @@IDENTITY, or for the current scope, with SCOPE_IDENTITY. However, it is also possible to find the latest identity inserted by any session.

IDENT_CURRENT

In a previous article I described how you can obtain the last inserted identity value using either @@IDENTITY or SCOPE_IDENTITY. These two functions allow you to obtain the identity column value for a row that you have just inserted using the current connection. They differ, as one returns the identity value explicitly inserted in the current scope, whilst the other can give you an identity from an insert that happens in a trigger, which is executed as a by-product of that action.

Sometimes you will want to find that latest identity value created for a table by any connection, even if you have not recently inserted a new row. This is possible using the IDENT_CURRENT function. To use the function you must provide the name of the table to be examined as the only argument. The latest identity for the named table is the return value of the function.

To demonstrate, create the following table in a test database:

CREATE TABLE TestTable
(
    ID INT IDENTITY(1,1)
)

Insert several rows with default values into the test table using the following statement:

INSERT INTO TestTable DEFAULT VALUES

Once you have inserted some rows you can use IDENT_CURRENT to find that latest identity value with the following command:

SELECT IDENT_CURRENT('TestTable')

Limitations

The number returned by IDENT_CURRENT is usually, but not necessarily, the highest identity value that exists in the table. There are two situations that prevent this from being true. The first is when the table is first created and no rows have been inserted. Calling the function for a new, empty table returns the seed value for the identity column. This is the next identity value that will be inserted.

The second case where the returned value will differ from the highest identity is when the latest insertion was made within a transaction that was rolled back. Identity updates not reversed by a rollback so the result of the function will be the value that would have been inserted in this situation. You can see this by running the following script and comparing the highest identity with the result from IDENT_CURRENT.

BEGIN TRAN
INSERT INTO TestTable DEFAULT VALUES
ROLLBACK TRAN
SELECT * FROM TestTable
SELECT IDENT_CURRENT('TestTable')

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Connecting to %s