Sunday, September 6, 2009

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:

image

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:

image

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:

image

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:

2 comments:

Peso said...

For a SQL Server 2000 alternative, see this
http://weblogs.sqlteam.com/peterl/archive/2009/07/16/How-to-get-a-batch-of-identity-values-without-OUTPUT.aspx

And as Adam mentioned, under certain parallell circumstances, SCOPE_IDENTITY can return wrong value. That can be remedied with OPTION (MAXDOP 1).

daspeac said...

it seems you have not heard about how do i recover data from sql server log files