Which is better: OUTPUT clause or old style insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Which is better: OUTPUT clause or old style insert

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-08-27 : 01:47:34

Hi, I am creating an audit trail and trying to figure out which one would be better in terms of performance, I believe Output should be better:

DECLARE @TBL TABLE (ID INT IDENTITY(1,1), NAME VARCHAR(10), AGE INT)
DECLARE @AUDIT TABLE (ID INT,NAME VARCHAR(10), AGE INT)

DECLARE @ID INT
DECLARE @NAME VARCHAR(10) = 'A'
DECLARE @AGE INT = 25

INSERT INTO @TBL (NAME,AGE)
OUTPUT inserted.ID, inserted.NAME, inserted.AGE INTO @AUDIT (ID, NAME, AGE)
VALUES (@NAME, @AGE)

--OR

INSERT INTO @TBL (NAME,AGE) VALUES (@NAME, @AGE)
SELECT @ID = SCOPE_IDENTITY()
INSERT INTO @AUDIT (ID, NAME,AGE) VALUES (@ID, @NAME, @AGE)

-- Similarly say for update:

--Using OUTPUT Clause
Update @tbl SET NAME ='B'
OUTPUT inserted.ID, inserted.NAME, inserted.AGE INTO @AUDIT (ID, NAME, AGE)
WHERE ID=1

--OR

Update @tbl SET NAME ='B' WHERE ID=1
INSERT INTO @AUDIT (ID, NAME,AGE)
SELECT ID, NAME, AGE FROM @TBL WHERE ID=1

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-08-27 : 03:10:47

I came across this in msdn:

http://technet.microsoft.com/en-us/library/ms177564.aspx

"
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."

Does this mean that output clause will always throw the error text back to the client even if I am inserting into some table. Can anyone please confirm this? I tried few tests but could not replicate this behavior.

--------------------
Rock n Roll with SQL

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 05:14:40

The following script will explain you the scenario what the statement means...

CREATE TABLE StudiesMaster1( StudiesId int, log_Ts DATETIME2 , Name varchar(10), PRIMARY KEY (StudiesId,Log_ts) )
CREATE TABLE StudiesMaster ( StudiesId int PRIMARY KEY, Name varchar(10) )

BEGIN TRY
insert into StudiesMaster output inserted.studiesId, getdate(), inserted.Name values(1, 'MS SQL') , (2, 'JAVA'), (3, 'ASP .NET')
SELECT * FROM TableNotExisted; -- here error will occur... Even though there is error in the TRY block Result from INSERTED is displayed to user
END TRY
BEGIN CATCH
SELECT @@ERROR
END CATCH

DROP TABLE StudiesMaster
DROP TABLE StudiesMaster1

--
Chandu

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-08-27 : 08:57:01

Thanks, got it now. Any answer to my first post?

--------------------
Rock n Roll with SQL

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 09:08:58

quote:
Originally posted by rocknpop

Thanks, got it now. Any answer to my first post?

--------------------
Rock n Roll with SQL

Welcome

Check the execution plan for both statements. then you will let you know.. Post us back your updates

--
Chandu

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-27 : 12:09:34

For a single row the OURPUT clause should be just fine. However, this is a case where I'd say a trigger is a better option for auditing a table.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 12:59:28

Take a look at this link:

[url]http://support.microsoft.com/kb/2019779[/url]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-27 : 13:15:53

For a single row, I think SCOPE_IDENTITY() will actually be slightly more efficient, since you don't have to insert into a table structure. But a single row is relatively trivial either way.

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 05:22:46

SCOPE_IDENTITY() would be better

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-08-30 : 01:46:38

Ok thanks guys for your answer. I believe most of the time it would be a single row DML operation.
I will post the execution plan for both.

Also, in case of Inserts how would scope_identity() perform in case of multiple simultaneous hits, it could return wrong identity in such a case. I was planning to throw OUTPUT result to a temp. table, get the newly inserted id from the temp table and then insert into audit from the temp table. Do you foresee any issue in this?

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 03:18:15

quote:
Originally posted by rocknpop

Ok thanks guys for your answer. I believe most of the time it would be a single row DML operation.
I will post the execution plan for both.

Also, in case of Inserts how would scope_identity() perform in case of multiple simultaneous hits, it could return wrong identity in such a case. I was planning to throw OUTPUT result to a temp. table, get the newly inserted id from the temp table and then insert into audit from the temp table. Do you foresee any issue in this?

--------------------
Rock n Roll with SQL

in case of multiple concurrent hits you've to do it inside a transaction to make sure you get correct id vales generated and retrieved

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-08-30 : 03:45:34

ok, so you mean scope_identity() would work if enclosed in a transaction even if there are multiple concurrent hits? Will it return correct newly inserted identity for that particular transaction only?

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources