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.