My Place
The Sahara of blogs…

I have long used SCOPE_IDENTITY() to get the primary key of a newly inserted row immediately after the INSERT statement. For example:

INSERT INTO myTable
SELET @var = SCOPE_IDENTITY()

What I incorrectly assumed was that the value was reset with each statement similar to the behavior of @@ERROR. If not each statement, then each INSERT statement. In other words, [@more@]if I insert zero records then I expected SCOPE_IDENTITY to return a null value. In actuallity, both SCOPE_IDENTITY and @@IDENTITY retain thier values until another identity value is generated.

As SQL Server Books Online says:

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. weblink

Run the following code against a server to see. (Should work against any 2000 or 2005 server.)

– ============================================
– TEST
 
– select identities
   
SELECT 'Before first insert:', @@IDENTITY '@@IDENTITY', SCOPE_IDENTITY()'SCOPE_IDENTITY()',@@ROWCOUNT '@@ROWCOUNT'
 
– Insert 10 records into table
INSERT INTO @tblTemp01 
SELECT TOP 10 SPECIFIC_NAME 
FROM INFORMATION_SCHEMA.ROUTINES
 
– select identities
SELECT 'After first insert:', @@IDENTITY '@@IDENTITY', SCOPE_IDENTITY()'SCOPE_IDENTITY()',@@ROWCOUNT '@@ROWCOUNT'
 
– Insert 0 records into table
INSERT INTO @tblTemp01
SELECT TOP 10 SPECIFIC_NAME 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE SPECIFIC_NAME = 'A_very_unlikely_name_to_RETURn_0_rows'
 
– select identities
SELECT 'After second insert:', @@IDENTITY '@@IDENTITY', SCOPE_IDENTITY()'SCOPE_IDENTITY()',@@ROWCOUNT '@@ROWCOUNT'
 
– select from table
SELECT * FROM @tblTemp01

aldkjfkljasd;lkfj a;ldkjf  a;lkdsfj