INF: Retrieving IDENTITY Value for Referential Integrity

 Last reviewed: April 30, 1997
Article 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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.