Output Clause – A Great Alternative To Scope_Identity()

In SQL Server 2005, a new clause was introduced that allows your query access to the inserted and deleted pseudo tables, without the use of a trigger.  In prior versions of SQL Server, the primary way to get a newly inserted identity value is to either use @@IDENTITY or Scope_Identity(). The new clause that became available in SQL Server 2005 is the Output Clause, http://technet.microsoft.com/en-us/library/ms177564.aspx.  The Output clause is a more reliable and cleaner solution for dealing with newly inserted identities.  The primary reason one should use the Output clause is reliability.  The scope_identity function can sometimes return a null value and @@identity may return an incorrect identity value.  I won’t go into detail about how scope_identity() and @@identity can return invalid results, but trust me it does happen.  Another great reason is scalability.  This is where I will be focusing my effort.  The Output clause is more scalable because @@Identity or Scope_Identity() function is limited to single row processing, while the output clause allows you to work with sets of data.  You may be asking yourself how-so?  The simple answer is scope_identity() allows you to get the newly inserted identity, within the scope (batch,trigger,stored procedure), or simple the newly inserted identity value; however, what happens when you need to get a collection of newly inserted identities? In order to process all of the the rows, a recursive or iterative process has to be implemented.  Let’s have a look how each method works and performs.

Sample Data:

IF OBJECT_ID('tempdb..#t') IS NOT NULL

BEGIN

    DROP TABLE #t;

END

GO

CREATE TABLE #t(

Id INT IDENTITY(1,1) PRIMARY KEY,

Col CHAR(1) NOT NULL

);

GO

IF OBJECT_ID('tempdb..#ValuesToInsert') IS NOT NULL

BEGIN

    DROP TABLE #ValuesToInsert;

END

GO

CREATE TABLE #ValuesToInsert(

Id INT,

val CHAR(1)

);

GO

----insert some values to insert

INSERT INTO #ValuesToInsert VALUES (1,'a');

INSERT INTO #ValuesToInsert VALUES (2,'b');

INSERT INTO #ValuesToInsert VALUES (3,'c');

Now let’s perform our first test, which will demonstrate how the scope_identity/@@Identity will yield incomplete results.

--INSERT into our table #t

INSERT INTO #t (col)

SELECT val FROM #ValuesToInsert;

--select the current identity, which does not include 1 and 2

--this means we need an iterative/recursive process to get each newly insert identity

SELECT @@IDENTITY AS [@@IDENTITY],SCOPE_IDENTITY() AS [SCOPE_IDENTITY()];

GO

Results:

We inserted three values into the table, not one. How do we accurately get the identity values for all inserted rows?  The answer is we have to come up with an iterative or recursive process, as shown below.

/*Test 2 - using a iterative process*/

IF EXISTS(SELECT 1 FROM #t)

BEGIN 

    TRUNCATE TABLE #t;

END

GO

DECLARE @i INT

DECLARE @NbrIns INT

SET @i = 1

SET @NbrIns    = (SELECT COUNT(*) FROM [#ValuesToInsert])

--loop through values to insert

WHILE @i <= @NbrIns

BEGIN

    --insert current values

    INSERT INTO #t (col)

    SELECT val FROM [#ValuesToInsert] WHERE [Id] = @i;

    --select the new identity value

    SELECT @@IDENTITY AS [@@IDENTITY],SCOPE_IDENTITY() AS [SCOPE_IDENTITY()];

    --increment the counter

    SET @i = @i + 1

END

GO

Results:

We all know that this is not highly scalable code.  This code will start to cause problems some where down the road, or is already affecting performance.  How can we make our code more scalable and better performing?  The saving grace is the Output clause.

/*Test 3 - Using the Ouput Clause*/

IF EXISTS(SELECT 1 FROM #t)

BEGIN 

    TRUNCATE TABLE #t;

END

GO

--Declare table to hold newly inserted ids

DECLARE @New_Ids TABLE(

Id INT

);

--INSERT into our table #t

INSERT INTO #t (col) OUTPUT inserted.Id INTO @New_Ids

SELECT val FROM #ValuesToInsert;

SELECT * FROM @New_Ids

Results:

There you have it.  We have successfully captured all of the newly inserted ids, while working with set based operations.  This is just a scratch on the surface of the power of the Output clause.  In a future post, I will go over how to the Output clause to capture insert, update, and delete rows.  Thanks for readings and as always, happy coding.

References:

• http://msdn.microsoft.com/en-us/library/ms190315.aspx
• http://msdn.microsoft.com/en-us/library/ms187342.aspx
• http://technet.microsoft.com/en-us/library/ms177564.aspx