SQL Server Table Identity Issues for Keys
Using @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT
STEP 1; SESSION 1: Initialize values for @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT
In order to prove the differences among @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT, the following statements (suggested by Microsoft Books Online) were executed in the CAMA database on an instance of SQL Server Management Studio (Session 1). The query creates the first empty table (t6) with the “id” identity defaulting to 1 when an insert occurs. The query creates a second table (t7) with the “id” identity defaulting to 100 when the first insert occurs (incremented by 1 thereafter). The initial identity values for both tables are then displayed.
The query continues by creating a trigger that inserts a new row in table t7 when an insert occurs in t6. The values of @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are then displayed immediately after both inserts occur.
USE CAMA;
GO
IF OBJECT_ID(N't6', N'U') IS NOT NULL
DROP TABLE t6;
GO
IF OBJECT_ID(N't7', N'U') IS NOT NULL
DROP TABLE t7;
GO
IF OBJECT_ID(N't8', N'U') IS NOT NULL
DROP TABLE t8;
GO
CREATE TABLE t6(id int IDENTITY);
CREATE TABLE t7(id int IDENTITY(100,1));
CREATE TABLE t8(id int IDENTITY(1000,1));
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END;
GO
/* End of trigger definition */
SELECT id AS t6ID FROM t6;
/* id is empty. */
SELECT id AS t7ID FROM t7;
/* ID is empty. */
SELECT id AS t8ID FROM t8;
/* ID is empty. */
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY AS [@@IDENTITY_Insert_T6];
/*Returns the value 100 from t7. This was inserted by the t6 trigger
as last transaction in session but not in scope.*/
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY_Insert_T6];
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/
SELECT IDENT_CURRENT('t7') AS [IDENT_CURRENT_Insert_T7];
/* Returns last (same as max) value inserted into t7,
that event though not in session or scope.*/
SELECT IDENT_CURRENT('t6') AS [IDENT_CURRENT_Insert_T6];
/* Returns value inserted into t6. This was the
INSERT statement four statements before this query.*/
The results are shown below:
The initial identities are shown as nulls in both tables before the inserts. After the inserts, @@IDENTITY shows 100 (the trigger value, not the value inserted into t6, since the insert is session and not scope related). The SCOPE_IDENTITY value shows up as 1 (the value of that inserted into table t6 since it was within the session and scope). The values of IDENT_CURRENT for t6 and t7 are shown correctly since they were the last values for the “id” identities inserted into t6 and t7 respectively. However, if another session had inserted values into the tables before they were selected, they may or may not be correct since session and scope are not part of IDENT_CURRENT’s operational parameters. The next session 2 query shows how this may be a real problem using IDENT_CURRENT.
STEP 2; SESSION 1:
In the same session,
/* Step 2 --------------------------------------------------------------------------*/
INSERT t6 DEFAULT VALUES;
INSERT t8 DEFAULT VALUES;
SELECT @@IDENTITY AS [@@IDENTITY_Insert_T8];
/*Returns the value 100 from t7. This was inserted by the t6 trigger
as last transaction in session but not in scope.*/
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY_Insert_T8];
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/
SELECT IDENT_CURRENT('t6') AS [IDENT_CURRENT_Insert_T6];
/* Returns last (same as max) value inserted into t7,
that event though not in session or scope.*/
SELECT IDENT_CURRENT('t8') AS [IDENT_CURRENT_Insert_T8];
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/
The results are:
Notice that @@IDENTITY and SCOPE_IDENTITY returned the last insert only (t8), but with IDENT_CURRENT, you can obtain the last insert into a table. This seems to indicate a good use for IDENT_CURRENT, but the problem is that if an insert fails, then you will get the last row inserted identity erroneously returned. The next session shows this.
SESSION 2: Displaying the same results from simple selects in a new session
In another instance of SQL Server Management Studio under a different session and login, simple selects viewed the values for @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT. We will run in a query window:
SELECT @@IDENTITY AS [@@IDENTITY];
/* Returns null since nothing was done in session to alter any table - either insert or update */
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
/* Returns null since nothing was done in session or scope to alter any table - either insert or update */
SELECT IDENT_CURRENT('t7') AS [IDENT_CURRENT_T7];
/* Returns last (same as max) value inserted into t7, even though nothing was inserted by this query */
SELECT IDENT_CURRENT('t6') AS [IDENT_CURRENT_T6];
/* Returns last (same as max) value inserted into t6 though nothing was inserted by this query */
The results are:
The results show that nothing was inserted, but IDENT_CURRENT displayed the last inserted value even though nothing was inserted. This is very similar to using the MAX value of the largest identity in a table. In the other two cases, both @@IDENTITY and SCOPE_IDENTITY displayed the correct values, but @@IDENTITY could have displayed an erroneous value if something had been inserted into a third table outside the scope but in the same session of the selects shown above.
Likewise, if for some reason, the insert had failed, then IDENT_CURRENT would still display the last inserted value for the identity even if nothing had happened. The MSDN SQL Server 2005 Books Online states:
“Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.”
Therefore, the best result for returning multiple values in the same scope and session are still with SCOPE_IDENTITY if assigned to variables after each insert:
Declare variables, and get SCOPE_IDENTITY after each insert; otherwise, if you wait until the end of the stored procedure, you only return the identity of the last insert:
BEGIN
DECLARE @id1 INT
DECLARE @id2 INT
INSERT t6 VALUES('Test 1');
SET @id1 = SCOPE_IDENTITY();
INSERT t8 VALUES('Test 2');
SET @id2 = SCOPE_IDENTITY();
SELECT @id1, @id2;
END
SQL .