T-SQL Output Clause

Question

T-SQL Output Clause

Ron McCullough

SSC Guru

Points: 63877
More actions
January 5, 2010 at 9:41 pm

#214165

Comments posted to this topic are about the item T-SQL Output Clause
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Rich Holt SSC-Addicted Points: 438 More actions · Answer 1

If, for some reason, you wanted to keep track of work regardless of the transaction state, you could use a table variable instead.

Table variables exist outside the scope of the transaction, and therefore are not rolled back.

For example:

DECLARE @CategoryChanges TABLE

( ChangeID int Primary Key Identity

, CategoryID int

, OldCategoryName nvarchar(15)

, NewCategoryName nvarchar(15)

, ModifiedDate datetime2

, LoginID nvarchar(30));

BEGIN TRANSACTION

UPDATE Categories

SET CategoryName = 'Dried Produce'

OUTPUT inserted.CategoryID, deleted.CategoryName

, inserted.CategoryName, getdate(), SUSER_SNAME()

INTO @CategoryChanges

WHERE CategoryID = 7;

SELECT * FROM @CategoryChanges --first select statement

ROLLBACK TRANSACTION

SELECT * FROM @CategoryChanges --second select statement

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

thanks bitbucket...

i got to know one more difference between temp tables and table variable.

Rich Holt SSC-Addicted Points: 438 More actions · Answer 3

:exclamation: I should also caution you about the other way to look at this:

If you insert/update/delete TABLE variable rows inside a transaction, and subsequently rollback the work, the table variable changes are not rolled back with the transaction. This could lead to data inconsistencies.

Be careful.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I’m sorry, but this question is not a good one. With the information that we got from the question, there is no way that can know how many records will be returned by the first select statement. The only thing that we can know for sure is that the second select statement will not return any records. Since answer included 2 optional answers that said that the second select statement will return no records and the only difference between them was the number of records that will be returned by the first select statement, I think that both of them can be regarded as correct answer.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 5

I don't think that the problem is that we can't know which is correct as Adi says, but that to know which is correct, you have to have Northwind installed, which not everybody does.

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 6

The question is trying to test the knowledge of temp tables with TSQL transactions, and does it very badly.

There is not enough information.

You should not require any database installed on your computer, and in fact should not even try to run the statement to answer for it would be cheating.

The question sucks.

There is one answer missing, which is correct -- UNKNOWN.

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

VALEK (1/6/2010)
The question is trying to test the knowledge of temp tables with TSQL transactions, and does it very badly.
There is not enough information.
You should not require any database installed on your computer, and in fact should not even try to run the statement to answer for it would be cheating.
The question sucks.
There is one answer missing, which is correct -- UNKNOWN.

Try not to take the question of the day so personally. I agree that the question has some issues and I didn’t like it, but you don’t have to be so blunt in your message. I also disagree that the only answer that could be correct is unknown. For example let take the same question and give you the fallowing options:

1)First select statements returned 10 records. Second returned 0 records

2)First select statement returned 10 records. Second returned 10 records.

3)First select statement returned 0 records. Second returned 10 records.

I think that given those options, the correct answer can be the first one (notice that unknown was not in the options).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

BudaCli Hall of Fame Points: 3404 More actions · Answer 8

Tx learned 1 or 2 things & also did it wif a table variable and saw different results as well.

DECLARE@CategoryChanges TABLE

(

ChangeID int Primary Key Identity ,

CategoryID int ,

OldCategoryName nvarchar(15),

NewCategoryName nvarchar(15),

ModifiedDate datetime,

LoginID nvarchar(30)

);

BEGIN TRAN

INSERT@CategoryChanges

SELECT1, 'Cat1', 'Cat2', GETDATE(), SUSER_SNAME()

SELECT * FROM @CategoryChanges

ROLLBACK TRAN

SELECT * FROM @CategoryChanges

What you don't know won't hurt you but what you know will make you plan to know better

john.curran.z3g Old Hand Points: 358 More actions · Answer 9

Ideally this question would have included the Categories table with the INSERT to remove the dependency on Northwind. This would also remove any doubt as to the number of records returned.

core-909671 SSC Veteran Points: 220 More actions · Answer 10

The temporary table does not contain a column "CategoryName". It attempts to update a column that does not exist. It would throw an error, not return rows.

john.curran.z3g Old Hand Points: 358 More actions · Answer 11

IF you have Northwind installed, the statement will update a Northwind Categories table and use the values from the inserted/deleted OUTPUT option to insert into the temp table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Rich Holt (1/6/2010)
:exclamation: I should also caution you about the other way to look at this:
If you insert/update/delete TABLE variable rows inside a transaction, and subsequently rollback the work, the table variable changes are not rolled back with the transaction. This could lead to data inconsistencies.
Be careful.

I agree, be careful; however, there may be a reason that you want the output from the insert/update/delete regardless of the success/failure of the transactions. If this is the case, then using table variables is required.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

mjohnson-909671 (1/6/2010)
The temporary table does not contain a column "CategoryName". It attempts to update a column that does not exist. It would throw an error, not return rows.

Take a closer look at the code. CategoryName is being updated in the Categories table. The OUTPUT clause is inserting the Old and New Category names into a temporary table as an audit of changes.

core-909671 SSC Veteran Points: 220 More actions · Answer 14

core-909671

SSC Veteran

Points: 220

January 6, 2010 at 6:41 am

#1099521

Ah. Ok. I don't have Northwind installed anywhere.