How to retrieve IDENTITY of a table

Background:
I have a Stored Procedure which need to insert data into a set of tables by maintaining the referential integrity. Meaning parent table ID value (ID is a Identity column) need to be fetched and used in child tables during insertion. There is a trigger defined in the parent table which inserts a record in another table. Logic in the SP is like 1. inserts a record in parent table 2. immediate statement retrieves last identity using @@IDENTITY global variable. As the trigger inserts record in another table, the @@IDENTITY variable returns the identity from second table. See Query-03 which represents the same scenario.


This problem drove me to investigate in detail about the approaches and their pros and cons to retrieve IDENTITY value of a table. 


Factors need to consider: (mostly for @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT())
Different scopes:

1. The session scope - the connection which produced the IDENTITY value
2. The statement scope - where is the statement that produced the IDENTITY value
3. The table scope -the able produced the IDENTITY value

What is scope?
A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.


What is @@IDENTITY?

1. @@IDENTITY is a system global variable
2. Returns the last IDENTITY value produced on a connection
3. Regardless of the table that produced the value
4. Regardless of the scope of the statement that produced the value
5. Returns IDENTITY of current session, ignores the scope

Note: From the attributes one thing is clear that developer has to be certain where the IDENTITY comes from and the scope of it unless same problem might occur which I explained above. (see Query-03 for sample code)


What are the alternatives?

1. Use  SCOPE_IDENTITY()
1. Attributes:
1. Returns the last IDENTITY value produced on a connection and by a statement in the same scope
1. Regardless of the table that produced the value
2. Returns IDENTITY of current session and current scope
2. Notes:
1. IDENTITY created in Trigger or User Defined Function for other table(s) won't affect actual table's IDENTITY
2. (see Query-03 for sample code)
2. Use  IDENT_CURRENT('tablename')
1. Attributes:
1. Returns last IDENTITY produced in a table
2. Regardless of the connection that created the value
3. regardless of the scope of the statement that produced the value
2. Notes:
1. IDENTITY created in Trigger or User Defined Function for other table(s) won't affect actual table's IDENTITY
2. If IDENTITY created in current scope is needed, this might not return correct value as some other connection might have already entered record in between
3. (see Query-03 for sample code)
3. Use OUTPUT clause:
1. Attributes:
1. Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE (see Query-01)
2. Information can be retrieved in a tabular form only, cannot be retrieved in a variable (see Query-02) 
3. Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed
2. Notes:
1. The Output clause is a new to SQL Server 2005
2. An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement
4. Use SEQUENCE feature in column (similar as Oracle Sequence)
1. Notes:
1. Available in SQL Server 2011 only
2. I couldn't come up with sample code as not used SQL Server 2011 yet
5. Use custom sequence (a table to store sequence value and a Stored Procedure to modify the value)
1. Notes:
1. Custom logic
2. Concurrency need to be handled manually

Resources:

1. How to use OUTPUT clause -  http://msdn.microsoft.com/en-us/library/ms177564.aspx
2. Problem in SCOPE_IDENTITY -  (1) https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value (2)  http://support.microsoft.com/kb/2019779

Sample Queries:


-------------
-- Query-01 - OUTPUT clause 
-------------
-- Data table
CREATE TABLE test(id INT IDENTITY(1,1), value VARCHAR(100));
-- table for output
DECLARE @outputTable TABLE (id INT);


INSERT INTO test(value) 
 OUTPUT inserted.id 
 INTO @outputTable
 VALUES('One');

SELECT * FROM test;
SELECT * FROM @outputTable;


-- Test OUTPUT with UPDATE
INSERT INTO test(value) VALUES('Two');
UPDATE test
 SET value = 'One Changed'
 OUTPUT deleted.id
 INTO @outputTable
 WHERE id=1
;
SELECT * FROM test;
SELECT * FROM @outputTable;


-- Test OUTPUT with DELETE
DELETE test
 OUTPUT deleted.id
 INTO @outputTable
 WHERE id=2;
SELECT * FROM test;
SELECT * FROM @outputTable;
GO


-------------
-- Query-02 - Output of OUTPUT clause in a variable
-- Result: it fails because OUTPUT returns data in tabular format
-------------
-- Data table
CREATE TABLE test(id INT IDENTITY(1,1), value VARCHAR(100));
DECLARE @outputVariable INT;


INSERT INTO test(value) 
 OUTPUT inserted.id 
 INTO @outputVariable
 VALUES('One');

SELECT * FROM test;
SELECT 'Variable= '+CONVERT(VARCHAR(100), @outputVariable);
GO


Error:
Msg 1087, Level 16, State 1, Line 6
Must declare the table variable "@outputVariable".


-------------
-- Query-03 - Usage of @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT()
-------------
-- Data tables
CREATE TABLE one(id INT IDENTITY(1,1), value VARCHAR(100));
CREATE TABLE two(id INT IDENTITY(1,1), value VARCHAR(100));
GO
-- Create trigger in table 'one'
CREATE TRIGGER TROne
 ON one
 AFTER INSERT
AS
DECLARE @value VARCHAR(100), @id INT;
SELECT @value=value, @id=id FROM inserted;

INSERT INTO two(value) VALUES('ID='+CONVERT(VARCHAR(100), @id) + ' is inserted in table one');
INSERT INTO two(value) VALUES('Value='+CONVERT(VARCHAR(100), @value) + ' is inserted in table one');
GO


INSERT INTO one(value) VALUES('Record one');
SELECT '@@IDENTITY= '+CONVERT(VARCHAR(100), @@IDENTITY);
SELECT '@@SCOPE_IDENTITY= '+CONVERT(VARCHAR(100), SCOPE_IDENTITY());
SELECT '@@IDENT_CURRENT for table one= '+CONVERT(VARCHAR(100), IDENT_CURRENT('one'));
SELECT '@@IDENT_CURRENT for table two= '+CONVERT(VARCHAR(100), IDENT_CURRENT('two'));
GO