nigel. (4/19/2010)

---

Andrew Watson-478275 (4/19/2010)

---

A more interesting result is from this:

DECLARE @sid int

DECLARE @table Table (KeyID int primary key,KeyData varchar(5))

DECLARE @idtable Table (KeyID int identity primary key,KeyData varchar(5))

INSERT INTO @idtable (KeyData) VALUES('Test1')

INSERT INTO @table (KeyID,KeyData) VALUES(1,'Test1')

INSERT INTO @table (KeyID,KeyData) VALUES(2,'Test2')

SET @sid = SCOPE_IDENTITY()

select @sid

This still returns NULL, even though there has been an identity insert and the latest operations were on tables without identities.

... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...