Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

T-SQL Output Clause Expand / Collapse
Author
Message
Posted Tuesday, January 05, 2010 9:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 PM
Points: 5,472, Visits: 23,525
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
Post #842538
Posted Tuesday, January 05, 2010 11:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 20, 2014 2:52 PM
Points: 91, Visits: 120
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
Post #842559
Posted Wednesday, January 06, 2010 12:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:13 AM
Points: 1,865, Visits: 368
thanks bitbucket...

i got to know one more difference between temp tables and table variable.
Post #842565
Posted Wednesday, January 06, 2010 1:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 20, 2014 2:52 PM
Points: 91, Visits: 120
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.
Post #842579
Posted Wednesday, January 06, 2010 1:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
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/
Post #842583
Posted Wednesday, January 06, 2010 2:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:21 AM
Points: 1,170, Visits: 2,153
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.
Post #842606
Posted Wednesday, January 06, 2010 2:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
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
Post #842624
Posted Wednesday, January 06, 2010 4:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
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/
Post #842673
Posted Wednesday, January 06, 2010 5:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 6:08 AM
Points: 1,076, Visits: 591
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
Post #842689
Posted Wednesday, January 06, 2010 5:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:25 AM
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.
Post #842704
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse