OUTPUT Clause in SQL Server 2005

God breathing through me

OUTPUT Clause in SQL Server 2005

Published on March 31, 2007

In previous versions of T-SQL you had to use a trigger to retrieve data from inserted, updated or deleted rows in tables which wasn't very straightforward. SQL Server 2005 adds a new clause to T-SQL to simplify this task. OUTPUT is this clause.

First suppose that I have a simple table like this:

CREATE TABLE MyTable
(
id INT IDENTITY PRIMARY KEY,
FullName nvarchar(20),
Age INT
)

OUTPUT clause works like a SELECT statement but its usage differs in INSERT, UPDATE and DELETE commands (I'll give examples to show its usage).

For INSERT commands you can simply put the OUTPUT clause inside the INSERT and use INSERTED table to get access to inserted data. For example INSERTED.id refers to inserted id column in MyTable. Below is an example of OUTPUT clause in INSERT command.

INSERT INTO MyTable
OUTPUT INSERTED.id AS InsertedID,
INSERTED.FullName AS InsertedFullName,
INSERTED.Age AS InsertedAge
VALUES ('Keyvan Nayyeri', 22)

Now I insert another row to use it in my samples.

INSERT INTO MyTable
OUTPUT INSERTED.id AS InsertedID,
INSERTED.FullName AS InsertedFullName,
INSERTED.Age AS InsertedAge
VALUES ('Gholi Javadzadeh', 24)

For UPDATE commands OUTPUT clause does the same job and retrieves data from updated rows but you can get access to old data with DELETED table and to new data with INSERTED table. For example DELETED.id retrieves the old value of id column and INSERTED.id retrieves the new value of id column. Below is an example of OUTPUT clause in UPDATE commands.

UPDATE MyTable
SET FullName = 'Javat Gholizadeh'
OUTPUT INSERTED.id AS ID,
DELETED.FullName AS OldFullName,
INSERTED.FullName AS NewFullName
WHERE id = 2

And finally for DELETE commands OUTPUT clause retrieves data from deleted rows. Using DELETED table you have access to data from deleted rows. For example DELETED.id refers to id column of deleted row. Here is an example of OUTPUT in DELETE command.

DELETE MyTable
OUTPUT DELETED.id AS DeletedID,
DELETED.FullName AS DeletedFullName,
DELETED.FullName AS DeletedAge
WHERE id = 2

Note that INSERTED and DELETED tables aren't physical tables. They're just two logical tables.

Now playing: Paco De Lucia - Cositas Buenas

9 Comments

DotNetKicks.com
Mar 31, 2007 10:19 PM
#

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Sep
Apr 11, 2007 3:28 PM
#

I ran some performance tests on the following 2 scenarios ... declare @counter int set @counter = 0 while @counter < 1000 begin set @counter = @counter + 1 INSERT INTO MyTable VALUES ('Keyvan Nayyeri', 22) end This loop took about 496 ms, while the following took about 10x more time at 5628 ms. declare @counter int set @counter = 0 while @counter < 1000 begin set @counter = @counter + 1 INSERT INTO MyTable OUTPUT INSERTED.id AS ID VALUES ('Keyvan Nayyeri', 22) end With that said, I suggest the use of 'output' only if absolutely needed.

Keyvan Nayyeri
Apr 25, 2007 10:27 PM
#

A few weeks ago I sent a post about OUTPUT Clause in SQL Server 2005 . One of other enhancements of T

Ralph Wilson
Sep 11, 2007 8:36 AM
#

Sep, I would seriously doubt whether anyone would be performing a series of, for exmple, 1000 INSERTs and getting the infromation back, as shown in your test, without making any use whatsoever of the returned data. That leads me to think that, while undoubtedly accurate as far as it goes, your test may still not be necessarily valid or it may not necessarily lead to a valid conclusion. If I am creating an application/stored prc that is making use of the OUTPUT feature, I will probably be doing other manipulations on or making other use of the returned data which will, effectively, decrease the impact of the expense of using the OUTPUT feature. Also, in light of the manner in which I would expect OUTPUT to be used, what is the relative/incremental expense of performing a corresponding query to retrieve the information that is returned by the use of the OUTPUT feature? Your test seems to be somewhat along the lines of comparing the cost of an airfare to the cost of the gasoline needed to drive from Dallas to Chicago. While the cost of the airfare may be more (leading to the conclusion that one should avoid flying from Dallas to Chicago if at all possible), the difference in the amount of time required for the trips may make the decision less obvious.

vps
Dec 10, 2007 1:34 AM
#

Wow... Good One for the Beginners to learn about OUTPUT Clause.

siva
Jan 17, 2008 12:39 AM
#

good

kah
Mar 20, 2008 2:48 PM
#

I ran a performance test looking at capturing & usng the data instead of just returning it. I looped each test 1000 times. My goal was to compare OUTPUT INTO as a method to collect the identity of records actually inserted into a table variable vs. using a field in the inserted table as "marker" to identify rows this process added and then having to clear that marker field later.

While the actual insert into the inserted table does take longer with OUTPUT INTO (which makes sense, you are adding to two tables instead of just one), the overall process was much faster then the alternative (1360 ms vs. 8346 ms).

sankar
Apr 01, 2008 1:18 AM
#

THANKS FO RUR HELP

Brian from Chicago
May 27, 2008 12:23 AM
#

"DELETED.FullName AS DeletedAge" was probably meant to read as "DELETED.Age AS DeletedAge".

Keyvan Nayyeri

Recent Posts

Archive

OUTPUT Clause in SQL Server 2005

Leave a Comment