I appreciate the amount of research that went into this question and I think it has a good educational value, but I don't think a QotD is the right form for this education - at least not a QotD in this specific form. That is because the education here is just in reading the answer, not in trying to find it yourself. It is simply nigh on impossible to find a complete list, because that would require one to know EVERYTHING
in the T-SQL syntax, or to read BOL entirely. Without putting in that effort (which is far too much to be worth it), you can only get this right by having a lucky guess.
Not to mock the author, but to prove my point that the T-SQL language is simply too complex to get away with ANY question of the "how many" kind, here are three constructs that are missing in the answer explanation:
1. UPDATE tablename
2. EXEC sp_executesql (this one is debatable; one could argue that this is covered by the "Output argument to a Procedure" in the answer, or you could argue that the dynamic naturre of sp_executesql makes this a distinct construct);
3. The default value assignment in a procedure definition: CREATE PROC MyProc
int = 12 AS ...
And I wouldn't be surprised at all if other people can extend this list even more.
EDIT: Posts by Carlo and IgorMi came in while I was writing, hence the duplication of the UPDATE consntruct. I read the QotD as excepting the SELECT and SET statements
, not the UPDATE statement which includes a SET clause
. (And I think the author had this in mind, given that he describes the OUTPUT INTO clause as "could count this 4 times" - if the SET clause is forbidden, the OUTPUT INTO can only be counted 3 times, because an UPDATE without SET is invalid)
Because I wanted to see the answers and weigh in on the discussion, I decided to take an "educated guess" at the question. I applied human psychology to eliminate the lowest and highest value from the answer options, then made a random pick from the remaining options. And I got lucky, so I now have another point to brag about to anyone who wants to hear (i.e., nobody).
Again, I do appreciate the effort that has gone in the question and the educational value from the explanation. I just think that this information was more suited for a short article, blog post, or similar. Or for a completely different form of question.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis