Scope and Identity

Most relational database management systems have the concept of an identity/auto-number attribute on an integer-like column. For the unwashed, visualize a "take a ticket" dispenser. Any time data is stored into the table, a number is pulled out and associated with that row. How do you capture that id? It's just the biggest number, right? Without knowing the built-in functionality of MSSQL, one may write a query on the order of "SELECT MAX(identity_column) FROM MyIdentTable" Depending on usage, that might work just fine but the possibility exists that a second record is inserted between the time the original record was inserted and the max query is run. In that case, you would receive the wrong id back from your query. That's a Bad Thing (tm).

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
IDENT_CURRENT
@@IDENTITY
SCOPE_IDENTITY

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]