SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / T-SQL Output Clause / Latest Posts

RE: T-SQL Output Clause

Peter Trast — Tue, 16 Mar 2010 14:20:58 GMT

I thought it was reasonable to assume that there would be at least one record in the table that would be returned with CategoryID = 7, so that left only one possible answer. Trick questions are lame and I never expect to see them in a professional forum. When you are not given enough info, it should be safe to assume many things that we are used to seeing.But then again, I got it right, so why would I be unhappy :-DGreat question, made me read carefully!

RE: T-SQL Output Clause

Michael Poppers — Thu, 07 Jan 2010 08:14:59 GMT

[quote][b]robert_edis (1/6/2010)[/b][hr]I am a little confused.If the UPDATE statement has a WHERE clause of CategoryID = 7 but no records have been previously loaded then it cannot write any data as the condition cannot be met.There the answer must be 0, 0.[/quote]Exactly...and WADR to Lynn et al., [u]that[/u] is "out of the box" thinking ;-). I'm glad approximately half of us thought the QotD was great, but my point is that the other half have legitimate objections to it, and IMHO the editor should consider such objections as criteria for future QotDs. Thanks.

RE: T-SQL Output Clause

Jinesh-I — Thu, 07 Jan 2010 06:15:30 GMT

Thanks a lot.:-)

RE: T-SQL Output Clause

Adi Cohn-120898 — Thu, 07 Jan 2010 04:30:39 GMT

[quote][b]jinesh.i (1/7/2010)[/b][hr]Hi,When I tried DECLARE @Table TABLE( Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1) , OldFirst_Name VARCHAR(20) , NewFirst_Name VARCHAR(20))BEGIN TRANUPDATE MSTR_UserSET First_Name=UPPER(First_Name)OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO @TableWHERE Matrix_User_Id =1294SELECT * FROM @TableROLLBACK TRANSELECT * FROM @TableIt returned row after ROLLBACK.and if I use --DECLARE @Table TABLECREATE TABLE #Table( Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1) , OldFirst_Name VARCHAR(20) , NewFirst_Name VARCHAR(20))BEGIN TRANUPDATE MSTR_UserSET First_Name=UPPER(First_Name)OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO #TableWHERE Matrix_User_Id =1294SELECT * FROM #TableROLLBACK TRANSELECT * FROM #TableIt returned 1 row before ROLLBACK and after it returned no row.It means Table variable retains the row while temp table does not.Can anybody share the reason behind this?Reply will be highly appreciated.:-D-Jinesh.[/quote]There are few difference between a table variable and temporary table. One of the difference is that temporary table is affected by transactions (e.g. rollback effect it) but table variables are not affected by transaction.Adi

RE: T-SQL Output Clause

Jinesh-I — Thu, 07 Jan 2010 01:45:15 GMT

Hi,When I tried DECLARE @Table TABLE( Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1) , OldFirst_Name VARCHAR(20) , NewFirst_Name VARCHAR(20))BEGIN TRANUPDATE MSTR_UserSET First_Name=UPPER(First_Name)OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO @TableWHERE Matrix_User_Id =1294SELECT * FROM @TableROLLBACK TRANSELECT * FROM @TableIt returned row after ROLLBACK.and if I use --DECLARE @Table TABLECREATE TABLE #Table( Matrix_User_ID INT --PRIMARY KEY IDENTITY(1,1) , OldFirst_Name VARCHAR(20) , NewFirst_Name VARCHAR(20))BEGIN TRANUPDATE MSTR_UserSET First_Name=UPPER(First_Name)OUTPUT inserted.Matrix_User_ID,deleted.First_name,inserted.First_name INTO #TableWHERE Matrix_User_Id =1294SELECT * FROM #TableROLLBACK TRANSELECT * FROM #TableIt returned 1 row before ROLLBACK and after it returned no row.It means Table variable retains the row while temp table does not.Can anybody share the reason behind this?Reply will be highly appreciated.:-D-Jinesh.

RE: T-SQL Output Clause

Tom Garth — Wed, 06 Jan 2010 16:58:56 GMT

RE: T-SQL Output Clause

robert_edis — Wed, 06 Jan 2010 16:47:15 GMT

I am a little confused.If the UPDATE statement has a WHERE clause of CategoryID = 7 but no records have been previously loaded then it cannot write any data as the condition cannot be met.There the answer must be 0, 0.

RE: T-SQL Output Clause

Lynn Pettis — Wed, 06 Jan 2010 16:36:01 GMT

That is a new date/time data type introduced in SQL Server 2008.

RE: T-SQL Output Clause

csecord — Wed, 06 Jan 2010 16:21:32 GMT

CREATE TABLE #CategoryChanges(ChangeID int Primary Key Identity , CategoryID int , OldCategoryName nvarchar(15) , NewCategoryName nvarchar(15) , ModifiedDate [b]datetime2 ??? [/b] , LoginID nvarchar(30));I have never heard of a data type of [b]datetime2[/b]

RE: T-SQL Output Clause

Tom Garth — Wed, 06 Jan 2010 15:50:27 GMT

Just because the question references a (the) Northwind db doesn't mean that you have to have one to answer the question. You have to figure that the question did fit the structure and data because it was a question about knowledge.I don't have Northwind, but guessed 1 and 1, and of course was wrong.That's what makes it a really good question. Maybe next time I'll get it right!

RE: T-SQL Output Clause

Cliff Jones — Wed, 06 Jan 2010 10:21:49 GMT

[quote][b]CirquedeSQLeil (1/6/2010)[/b][hr]Nice Question - with or without the Northwind DB (which I do not have). A logical approach on this question should reveal the answer.[/quote]I agree.

RE: T-SQL Output Clause

SQLRNNR — Wed, 06 Jan 2010 10:14:01 GMT

Nice Question - with or without the Northwind DB (which I do not have). A logical approach on this question should reveal the answer.

RE: T-SQL Output Clause

Cliff Jones — Wed, 06 Jan 2010 10:06:00 GMT

This is a good question. I was able to get it right without having the Northwind database however I had to assume that there was 1 and only 1 record where CategoryID = 7. But without knowing how many records 0, 1 or more (where CategoryID = 7) you can't answer the question since both answer 1 or 2 could be correct.

RE: T-SQL Output Clause

sknox — Wed, 06 Jan 2010 09:41:57 GMT

[quote][b]Michael Poppers (1/6/2010)[/b][hr][quote][b]john.curran.z3g (1/6/2010)[/b][hr]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.[/quote]Exactly. Nothing in the QotD told me what was previously in Categories, and for the questioner to assume that its contents were known (at least insofar as which row(s), if any, matched the WHERE clause of the UPDATE) is (to be blunt) improper.[/quote]NOT AT ALL.Let's generalize:You are given a database [d] with a table named [t].One of the columns in table [t] is named [t_ID].Either [t_ID] is the identifying column for table [t], or the creator of table [t] (intentionally or unintentionally) made the column name counterintuitive.Since the Northwind database is a demonstration database (or, if you wish to presume complete ignorance of the Northwind database, since the question is a sincere one, not a trick), the second conclusion is unlikely at best. As a result, it is logical to assume that CategoryID is an identifier column, and an = query would return 0 or 1 row. Since the focus of the question was on the OUTPUT clause, it is logical to assume that 1 row would be returned, as 0 rows returned would effectively negate the use of that clause.So, if you start from the assumption that this QotD is a real knowledge testing question, and not a trick question, the question makes sense, and gives excellent insight into a useful aspect of SQL Server.If you start from the assumption that the QotD is a trick question, well, that's just a little sad.

RE: T-SQL Output Clause

Lynn Pettis — Wed, 06 Jan 2010 08:57:55 GMT

RE: T-SQL Output Clause

Oleg Netchaev — Wed, 06 Jan 2010 08:54:19 GMT

[quote][b]Adi Cohn-120898 (1/6/2010)[/b][hr]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...Adi[/quote][b]This is an excellent question[/b], and your statement about no way to know how many records will be returned by the first select is not exactly correct. I believe that majority of this site members work with SQL Server 2005 or better and therefore, they most likely don't have Northwind database. They don't have to have it though to still be able to answer the question (correctly or not). The questions of the day are to test our knowledge, not the copy/paste abilities. Coming back to your statement, please note that the name of the table is Categories, the predicate calls for selection of the record with the CategoryID equal to 7, and from what I remember from the good old days of the last millennium, the aforementioned field is a primary key of Categories table. Thus, there can only be one record satisfying the predicate. Surely it is possible that Categories table does not have a record with CategoryID = 7 which in turn would make the 0 records, 0 records a correct answer, but this would be a cold thing to do for the author of the question and would also completely change its purpose.I really liked the question.Oleg

RE: T-SQL Output Clause

Michael Poppers — Wed, 06 Jan 2010 08:31:00 GMT

[quote][b]john.curran.z3g (1/6/2010)[/b][hr]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.[/quote]Exactly. Nothing in the QotD told me what was previously in Categories, and for the questioner to assume that its contents were known (at least insofar as which row(s), if any, matched the WHERE clause of the UPDATE) is (to be blunt) improper.

RE: T-SQL Output Clause

core-909671 — Wed, 06 Jan 2010 07:39:01 GMT

Gee.thanks. First time I ever post... thanks a ton. You're the best!

RE: T-SQL Output Clause

dmbaker — Wed, 06 Jan 2010 07:29:56 GMT

I know, I got the question right. Just being snarky. :-P

RE: T-SQL Output Clause

Lynn Pettis — Wed, 06 Jan 2010 07:23:59 GMT

[quote][b]dmbaker (1/6/2010)[/b][hr]Where was the choice for "Could not locate entry in sysdatabases for database 'Northwind'."?[/quote]Actually irrelevant to answering the question.

RE: T-SQL Output Clause

dmbaker — Wed, 06 Jan 2010 07:07:54 GMT

Where was the choice for "Could not locate entry in sysdatabases for database 'Northwind'."?

RE: T-SQL Output Clause

Lynn Pettis — Wed, 06 Jan 2010 06:45:16 GMT

Neither do I.

RE: T-SQL Output Clause

core-909671 — Wed, 06 Jan 2010 06:41:28 GMT

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

RE: T-SQL Output Clause

Lynn Pettis — Wed, 06 Jan 2010 06:39:27 GMT

[quote][b]mjohnson-909671 (1/6/2010)[/b][hr]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.[/quote]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.

RE: T-SQL Output Clause

Lynn Pettis — Wed, 06 Jan 2010 06:36:19 GMT

[quote][b]Rich Holt (1/6/2010)[/b][hr]: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 [b]table variable changes are not rolled back with the transaction[/b]. This could lead to data inconsistencies. Be careful.[/quote]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.

RE: T-SQL Output Clause

john.curran.z3g — Wed, 06 Jan 2010 06:15:50 GMT

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.

RE: T-SQL Output Clause

core-909671 — Wed, 06 Jan 2010 05:59:42 GMT

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.

RE: T-SQL Output Clause

john.curran.z3g — Wed, 06 Jan 2010 05:55:29 GMT

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.

RE: T-SQL Output Clause

BudaCli — Wed, 06 Jan 2010 05:16:03 GMT

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 TRANINSERT @CategoryChanges SELECT 1, 'Cat1', 'Cat2', GETDATE(), SUSER_SNAME() SELECT * FROM @CategoryChanges ROLLBACK TRANSELECT * FROM @CategoryChanges

RE: T-SQL Output Clause

Adi Cohn-120898 — Wed, 06 Jan 2010 04:26:11 GMT

[quote][b]VALEK (1/6/2010)[/b][hr]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.[/quote]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 records2) 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

RE: T-SQL Output Clause

VALEK — Wed, 06 Jan 2010 02:52:04 GMT

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.

RE: T-SQL Output Clause

Andrew Watson-478275 — Wed, 06 Jan 2010 02:26:00 GMT

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.

RE: T-SQL Output Clause

Adi Cohn-120898 — Wed, 06 Jan 2010 01:24:42 GMT

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

RE: T-SQL Output Clause

Rich Holt — Wed, 06 Jan 2010 01:00:30 GMT

: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 [b]table variable changes are not rolled back with the transaction[/b]. This could lead to data inconsistencies. Be careful.

RE: T-SQL Output Clause

ziangij — Wed, 06 Jan 2010 00:09:47 GMT

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

RE: T-SQL Output Clause

Rich Holt — Tue, 05 Jan 2010 23:51:43 GMT

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 [b]@CategoryChanges TABLE [/b]( ChangeID int Primary Key Identity , CategoryID int , OldCategoryName nvarchar(15) , NewCategoryName nvarchar(15) , ModifiedDate datetime2 , LoginID nvarchar(30));BEGIN TRANSACTIONUPDATE CategoriesSET CategoryName = 'Dried Produce'OUTPUT inserted.CategoryID, deleted.CategoryName , inserted.CategoryName, getdate(), SUSER_SNAME() INTO [b]@CategoryChanges[/b]WHERE CategoryID = 7;SELECT * FROM [b]@CategoryChanges[/b] --first select statementROLLBACK TRANSACTIONSELECT * FROM [b]@CategoryChanges[/b] --second select statement

T-SQL Output Clause

bitbucket-25253 — Tue, 05 Jan 2010 21:41:38 GMT

Comments posted to this topic are about the item [B]T-SQL Output Clause[/B]