An idiomatic way of capturing that within SQL Server is to use one of the three native features: SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT.
IDENT_CURRENT('TableName') will return the last identity value generated for a table. If none have been inserted, or it's been truncated, the function returns the seed value. This can be called at any time, even if the last identity was generated a year and a hundred powercycle ago. It is callable per table and if the table in question doesn't have an identity column, it will return NULL. Do not attempt to use this function to guess what the next identity value will be. You might get right 99 times out of 100 but when you are wrong, you will spend far too much of your life trying to rectify the problem of out of synch data.
@@identity returns the last identity value created in that session (connection). SCOPE_IDENTITY returns the last identity value created in that session. If an identity has not been generated in the session, both methods will return NULL. They are not synonyms however, as they will generate different values based on your scope. Just like in programming, the concept of variable scope applies. @@identity is a global variable, scope_identity captures locally scoped value. Using the imagery from above, imagine pulling the ticket also caused a different ticket dispenser to dispense a number. I pull 63 and dispenser B spits out 103. In a pre-SQL Server 2008 world, we rolled our own change data capture by creating audit tables that were inserted into whenever data changed. An address is created. Under the sheets, a record is inserted into dbo.ADDRESS and the address_id identity value, 63, is created. An insert trigger is also fired that writes all the data to the audit table along with metadata indicating it's an insert. The audit table also has an identity value, 103. SCOPE_IDENTITY() will return 63, while @@identity will yield 103.
Unless you have a reason to do it otherwise, stick with SCOPE_IDENTITY()
Tasty bits from BOL
"SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).
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."
"IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope."
Links to BOL
DROP TABLE dbo.MyIdentTable DROP TABLE dbo.RedHerring GO CREATE TABLE dbo.MyIdentTable ( myIdent int identity(100, 10) NOT NULL PRIMARY KEY CLUSTERED , col1 varchar(50) NOT NULL ) GO CREATE TABLE dbo.RedHerring ( herring_id int identity(10, 1) NOT NULL PRIMARY KEY CLUSTERED , col2 varchar(50) NOT NULL ) GO -- Seed value of 100 SELECT ident_current('dbo.myidenttable') AS current_ident GO -- Add 2 rows, show output INSERT INTO dbo.MyIdentTable OUTPUT INSERTED.myIdent AS [INSERTED_myIdent], INSERTED.col1 [INSERTED_col1] SELECT 'A' AS col1 UNION ALL SELECT 'B' GO -- Note same value regardless of which function is called SELECT @@identity AS [identity] , scope_identity() AS [scope_identity] , ident_current('dbo.myidenttable') AS [ident_current] GO SELECT * FROM dbo.MyIdentTable GO -- Insert fails, but identity still claimed INSERT INTO dbo.MyIdentTable SELECT NULL AS col1 GO -- Note same value regardless of which function is called SELECT @@identity AS [identity] , scope_identity() AS [scope_identity] , ident_current('dbo.myidenttable') AS [ident_current] GO INSERT INTO dbo.RedHerring SELECT 'z' GO -- Note same value regardless of which function is called SELECT @@identity AS [identity] , scope_identity() AS [scope_identity] , ident_current('dbo.myidenttable') AS [ident_current] GO
[edit date="2009-10-27"]PSS has a good article discussing how to handle identity values pre-SQL 7. I didn't realize you can chain a variable assignment and an update together ala "set @iVal = iVal = iVal + 1" I had always assumed it was one or the other, but both works.[/edit]