Presents your 
SQL SERVER E-NEWSLETTER for February 11, 2003

<------------------------------------------->

UNDERSTAND THE GLOBAL VARIABLE @@ IDENTITY

The global variable @@IDENTITY contains the last value of a column that
contains an identity property. This is the last value within the user's
session. What affects this value is that the transaction may contain
triggers that insert into other tables containing the identity property. In
that case, the @@IDENTITY global variable would contain the value of 
the last inserted table containing the identity property.

Another scenario causing @@IDENTITY to be affected is when an INSERT is
issued against a table that does not contain the identity property. In
this case, the @@IDENTITY value would be null.

The function SCOPE_IDENTITY() returns the last identity value within a
module. The module would pertain to a batch, stored procedure or trigger.
In the case of inserting into a table that then fires a trigger and
inserts into another table that has the identity property, 
SCOPE_IDENTITY()
would only pertain to the first insert, not the trigger inserting into
another table.

The function IDENT_CURRENT(<table name>) is limited to the table and
will return the last identity value for the specified table.

Both the SCOPE_IDENTITY and IDENT_CURRENT functions are in SQL Server
2000 but not in prior versions.

The script below demonstrates how the @@IDENTITY global variable is
affected by a trigger. The script inserts into one table containing the
identity property, and a trigger is fired that inserts into another table
that has the identity property. The identity value is from the second table
being inserted into and not the original table.

The script also demonstrates the effect of inserting into a table that
does not have the identity property, causing the @@IDENTITY global
variable to return NULL. In addition, the script demonstrates the use 
and differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT().

SET NOCOUNT ON
GO
CREATE TABLE T1
(PKEY1 INT IDENTITY NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
COL1 INT NULL)
GO
CREATE TRIGGER TI_T1 ON T1
FOR INSERT AS
BEGIN
  INSERT T2 (COL1) VALUES ( 1 )
END
GO

CREATE TABLE T2
(PKEY1 INT IDENTITY NOT NULL CONSTRAINT PK_T2 PRIMARY KEY,
COL1 INT NULL)
go

CREATE TABLE T3
(PKEY1 INT NOT NULL CONSTRAINT PK_T3 PRIMARY KEY,
COL1 INT NULL)
go

INSERT T2 (COL1) VALUES ( 1 )
GO
INSERT T2 (COL1) VALUES ( 1 )
GO
INSERT T2 (COL1) VALUES ( 1 )
GO

INSERT T1 (COL1) VALUES ( 1 )
GO
SELECT @@IDENTITY IdentityGV,
     SCOPE_IDENTITY( ) ScopeIdentity,
     IDENT_CURRENT('T1') IdentCurrent
GO

INSERT T3 (PKEY1) VALUES ( 1 )
GO
SELECT @@IDENTITY IdentityGV,
     SCOPE_IDENTITY( ) ScopeIdentity,
     IDENT_CURRENT('T1') IdentCurrent
GO

DROP TABLE T1,T2,T3
GO

----------------------------------------