Friday, January 02, 2004

Fetching IDENTITY value ...

The question is "How to fetch the IDENTITY value of the record which we INSERTED now?". Till the days of SQL Server 7.0 we used to rely on @@IDENTITY function. But in my experience that function isn't that reliable. i.e., @@IDENTITY isn't dependent on the current scope. Even if we have inserted some records in a different table it would fetch us that identity value ;) I am sure we wouldn't be interested in that.

From SQL 2K there is a new function by name SCOPE_IDENTITY which returns the last IDENTITY value produced on a connection and by a statement in the same scope. So its better to use SCOPE_IDENTITY in our select statement to retrieve the identity value for the record which we inserted now.

Example: SELECT SCOPE_IDENTITY()

No comments: