Using The Output Clause

In a prior post,http://jahaines.blogspot.com/2009/09/output-clause-great-alternative-to.html , I described a great alternative to scope identity, which is the Output clause, http://technet.microsoft.com/en-us/library/ms177564.aspx. In this post I will describe how to use the Output clause in a manner other than getting identity values.  The Output clause is a new to SQL Server 2005.  The biggest benefit of this clause it allows a developer access to the DML pseudo tables, inserted and deleted.  Typically these pseudo tables are only available to DML triggers.  Now that I laid the groundwork, I will create our table structures.

USE [tempdb]

GO

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

BEGIN

    DROP TABLE dbo.t;

END

GO

CREATE TABLE dbo.t(

Id INT IDENTITY(1,1) PRIMARY KEY,

Col CHAR(1)

);

IF OBJECT_ID('tempdb.dbo.DML') IS NOT NULL

BEGIN

    DROP TABLE dbo.DML;

END

GO

CREATE TABLE dbo.DML(

DML_Type CHAR(1),

Id INT,

Col CHAR(1)

);

Now that I have created our table structure, I can create our first DML transaction.  Our first DML transaction will be an insert statement where I use the values clause.  In this example and in the subsequent examples, I will be performing different DML transactions and inserting the affected row data and transaction type into the DML table.

--Insert a single value into the table dbo.t

INSERT INTO dbo.t (col) OUTPUT 'I',INSERTED.Id,INSERTED.Col INTO dbo.DML VALUES ('a');

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

*/

As you can see from the output above, I have used the output clause to successfully capture all the row data.  The next DML transaction I will demonstrate is a insert statement, using a select statement.

--Insert multiple values into the table dbo.t

INSERT INTO dbo.t (col) OUTPUT 'I',INSERTED.Id, INSERTED.Col INTO dbo.DML

SELECT 'b' UNION ALL

SELECT 'c'

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

I        2           b

I        3           c

*/

Next, I will demonstrate a delete statement.  I will be deleting all the rows, except id 2.

--Delete all ids but 2 from dbo.t

DELETE FROM dbo.t OUTPUT 'D',DELETED.Id,DELETED.Col INTO dbo.DML

WHERE id <> 2

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

I        2           b

I        3           c

D        1           a

D        3           c

*/

In he last example, I will be demonstrating an Update statement.  One thing to remember about an Update statement is the Output Clause has access to both the INSERTED and DELETED pseudo tables. It should be noted that I am only getting data from the INSERTED table.

--Update id = 2 the table dbo.t

UPDATE dbo.t

SET Col = 'E'

OUTPUT 'U', INSERTED.Id,INSERTED.[Col] INTO dbo.DML

WHERE Id = 2

--Select all rows from the table dbo.DML

SELECT * FROM dbo.DML

/*

DML_Type Id          Col

-------- ----------- ----

I        1           a

I        2           b

I        3           c

D        1           a

D        3           c

U        2           E

*/

There you have it.  The Output clause is a very powerful clause that allows developers a method to obtain data that was once difficult to get.  This clause can be used in a lot of different scenarios and situations, but I leave it to you to find out how this can be used in your environment.

Happy Coding.

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