T-SQL Output Clause

Question

Post reply

T-SQL Output Clause

Viewing 15 posts - 16 through 30 (of 36 total)

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

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

Lynn Pettis

SSC Guru

Points: 442467

January 6, 2010 at 6:45 am

#1099524

Neither do I.

dmbaker SSCertifiable Points: 5139 More actions · Answer 2

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

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

dmbaker (1/6/2010)
Where was the choice for "Could not locate entry in sysdatabases for database 'Northwind'."?

Actually irrelevant to answering the question.

dmbaker SSCertifiable Points: 5139 More actions · Answer 4

I know, I got the question right. Just being snarky. 😛

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

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

Michael Poppers SSCrazy Points: 2151 More actions · Answer 6

john.curran.z3g (1/6/2010)
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.

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.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 7

Adi Cohn-120898 (1/6/2010)
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

This is an excellent question, 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

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

Michael Poppers (1/6/2010)
john.curran.z3g (1/6/2010)
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.
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.

Just needed to think outside the box a little, that's all.

sknox SSChampion Points: 12388 More actions · Answer 9

Michael Poppers (1/6/2010)
john.curran.z3g (1/6/2010)
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.
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.

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.

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

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

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

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

I agree.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 13

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!

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

csecord Mr or Mrs. 500 Points: 513 More actions · Answer 14

CREATE TABLE #CategoryChanges

(ChangeID int Primary Key Identity

, CategoryID int

, OldCategoryName nvarchar(15)

, NewCategoryName nvarchar(15)

, ModifiedDate datetime2 ???

, LoginID nvarchar(30));

I have never heard of a data type of datetime2

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

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