IDENTITY Results Clarification

SeleccTViews
With Stephen Wynkoop
Watch as Scott Gode from Azaleos talks with Stephen about gaps between private cloud systems and Office 365.

IDENTITY Results Clarification
I received an Email correcting my description on the behavior of both SCOPE_IDENTITY and @@IDENTITY. Also, there was a twitter response regarding the behavior of IDENT_CURRENT as well.

Don Writes:
In Getting rid of @@Identity you stated “@@IDENTITY is a global server wide variable returning the last identity value assigned in any table by any connection having completed an insert at the time you read the property.” That is not true. @@Identity is limited to the session, so it will return the last Identity inserted by the session. Scope_Identity is limited to the scope (or batch). Thus the only time @@Identity will return incorrect data is if you have a trigger.

I agree that your proposed solution is a good one and I’ve found myself making pretty extensive use of OUTPUT, but I just wanted to clarify that statement.

Rather than working from memory I decided to create a series of SQL Commands validating how the different methods of identifying IDENTIY values work. When I first started it seemed pretty simple. Later, as I tried different combinations, different results began to emerge.

Here is my base test. I create two tables. I add one row to the first table, then add two more rows to the first table through an EXEC command, pushing the action into a different Command Batch.

-- Test Method 1
IF OBJECT_ID('TESTA') IS NOT NULL BEGIN EXEC ('DROP TABLE TESTA') END
IF OBJECT_ID('TESTB') IS NOT NULL BEGIN EXEC ('DROP TABLE TESTB') END

CREATE TABLE TESTA (ID INT NOT NULL IDENTITY(1,1), Value CHAR(1) NOT NULL)
CREATE TABLE TESTB (ID INT NOT NULL IDENTITY(10,1), Value CHAR(1) NOT NULL)

INSERT INTO TESTA SELECT 'A'
EXEC ('
INSERT INTO TESTA SELECT ''B'';
INSERT INTO TESTA SELECT ''C'';
--Test values 1
SELECT Results=''1'', [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTIT(),
[IDENT_CURRENTA] = IDENT_CURRENT(''TESTA''),
[IDENT_CURRENTB] = IDENT_CURRENT(''TESTB'');
')
SELECT Results = '2', [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT('TESTA'),
[IDENT_CURRENTB] = IDENT_CURRENT('TESTB')

This makes sense to me. @@Identity reflects the value for everything that has occurred. Scope Identity is restricted to the command batch under which it was executed. IDENT_CURRENTA reflects the current value of the table. However, IDENT_CURRENTB reflects the seed value of TABLEB, since no records have yet been added to that table.

Now I modify the previous series of statements, adding a row to TESTB as well as the rows to TESTA. I do this insert in the middle of the EXEC statement forcing it to become a part of the second batch. Let’s see how this series of commands impact the different variables.

— Test Method 2
IF OBJECT_ID(‘TESTA’) IS NOT NULL BEGIN EXEC (‘DROP TABLE TESTA’) END
IF OBJECT_ID(‘TESTB’) IS NOT NULL BEGIN EXEC (‘DROP TABLE TESTB’) END

CREATE TABLE TESTA (ID INT NOT NULL IDENTITY(1,1), Value CHAR(1) NOT NULL)

CREATE TABLE TESTB (ID INT NOT NULL IDENTITY(10,1), Value CHAR(1) NOT NULL)

INSERT INTO TESTA SELECT ‘A’

EXEC (‘
INSERT INTO TESTA SELECT ”B”;
INSERT INTO TESTA SELECT ”C”;
–Test values 1
SELECT Results=”1”, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(”TESTA”),
[IDENT_CURRENTB] = IDENT_CURRENT(”TESTB”);

INSERT INTO TESTB SELECT ”A”;

SELECT Results=”2”, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(”TESTA”),
[IDENT_CURRENTB] = IDENT_CURRENT(”TESTB”);
‘)

SELECT Results = ‘3’, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(‘TESTA’),
[IDENT_CURRENTB] = IDENT_CURRENT(‘TESTB’)

Things are starting to get a little more complicated. Granted, the contributing factors are all under my control. But it is becoming more important for me to remember the impact of each command.

For my final test, I want to add the impact of a completely different process, emulating a different user. Unlike using a different batch through an EXEC statement, I am actually creating another database connection that should share nothing in the variables; only IDENT_CURRENT values should be shared.

— Test Method 3
IF OBJECT_ID(‘TESTA’) IS NOT NULL BEGIN EXEC (‘DROP TABLE TESTA’) END
IF OBJECT_ID(‘TESTB’) IS NOT NULL BEGIN EXEC (‘DROP TABLE TESTB’) END

CREATE TABLE TESTA (ID INT NOT NULL IDENTITY(1,1), Value CHAR(1) NOT NULL)

CREATE TABLE TESTB (ID INT NOT NULL IDENTITY(10,1), Value CHAR(1) NOT NULL)

INSERT INTO TESTA SELECT ‘A’

EXEC (‘
INSERT INTO TESTA SELECT ”B”;
INSERT INTO TESTA SELECT ”C”;
–Test values 1
SELECT Results=”1”, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(”TESTA”),
[IDENT_CURRENTB] = IDENT_CURRENT(”TESTB”);

INSERT INTO TESTB SELECT ”A”;

SELECT Results=”2”, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(”TESTA”),
[IDENT_CURRENTB] = IDENT_CURRENT(”TESTB”);
‘)

— Run these next two lines in different query window
INSERT INTO TESTB SELECT ‘B’

SELECT Results = ‘3’, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(‘TESTA’)
[IDENT_CURRENTB] = IDENT_CURRENT(‘TESTB’)

— Come back and run this next line in the original query window
SELECT Results = ‘4’, [@@IDENTITY] = @@IDENTITY,
[SCOPE_IDENTITY] = SCOPE_IDENTITY(),
[IDENT_CURRENTA] = IDENT_CURRENT(‘TESTA’)
[IDENT_CURRENTB] = IDENT_CURRENT(‘TESTB’)

Everything is working in a predictable manner. I definitely have to be aware of the impact of other processes on my expected results.

SCOPE_IDENTITY() is the most restrictive. It represents the results of queries in the current batch. Using the EXEC command I created a separate batch, resulting in a different SCOPE_IDENTITY() value generated within that batch. Likewise, when I executed the query in another query window, it again had isolated results. For this reason, SCOPE_IDENTITY() is a preferred method.

@@IDENTITY was global to a connection or ProcessID. All actions under that process produce the @@IDENTITY result. In the third test, the final @@IDENTITY value represents the last IDENTITY value assigned in that connection or process. The @@IDENTITY value of 11 only appears in the second query window.

Notice also that @@IDEDNTITY nor SCOPE_IDENTITY() are specific to a table. IDENT_CURRENT(‘’) Always returns the current value of an identity column for a table. However, this value is not restricted by process or batch.

So, if you need the IDENTITY value assigned in any table and don’t want to come back to this newsletter to figure out the sequence of events, you can always rely on the OUTPUT clause of an INSERT statement to return specific IDENTITY values.

Reader Comments
Fadhl Writes:
I really appreciate your effort. It is very important tip.

David Writes:
I thought the “INCLUDE” feature was a part of SQL 2005? Or was it the part that states "and not include the column as a part of the index", the part that makes it specific to SQL 2008?

Well, I think that covers @@IDENTITY now. Now you have some feedback to help you know where you may need to use it.

Send your Comments or ideas for this or any other topic to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software

When you’re looking for a reliable tool to diagnose … (read more)