INF: Retrieving IDENTITY Value for Referential Integrity

ID: Q134660

The information in this article applies to:

Microsoft SQL Server version 6.0

SUMMARY

The Microsoft SQL Server version 6.0 documentation says that you can use the @@IDENTITY variable to retrieve the last-inserted identity value. The documentation, however, does not completely state the scope of the @@IDENTITY variable.

This article provides some additional information to clarify the scope of the @@IDENTITY variable.

MORE INFORMATION

The @@IDENTITY variable returns the value of the last inserted identity value.

The variable is unique to each connection.

The @@IDENTITY variable is updated each time you perform and insert into a table containing an identity column. This will include inserts that take place in an INSERT trigger. This means you are able to capture the value of the @@IDENTITY variable immediately following the insert operation and use it for foreign key references.

Also, inserts taking place on other connections are assigned the proper IDENTITY values, but your copy of the @@IDENTITY variable reflects only your last inserted IDENTITY value.

Additional query words: sql6 global variable


Keywords          : kbprg kbusage SSrvDRI SSrvProg SSrvTran_SQL 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 16, 1999