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.
I believe this is simply an issue with the documentation, as BOL is much clearer on the @@IDENTITY function, it says:
... If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. ...
I would expect the only significant difference between @@IDENTITY and SCOPE_IDENTITY to be their scope.