Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Output Clause


T-SQL Output Clause

Author
Message
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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

Before posting a performance problem please read
Rich Holt
Rich Holt
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 194
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
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2906 Visits: 374
thanks bitbucket...

i got to know one more difference between temp tables and table variable.
Rich Holt
Rich Holt
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 194
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
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 6076
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
Andrew Watson-478275
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1657 Visits: 2653
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
VALEK
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 462
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
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 6076
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
BudaCli
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 598
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
SELECT   1, '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
john.curran.z3g
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 53
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search