April 27, 2013 at 8:55 pm
Comments posted to this topic are about the item QUERY and TABLE HINTS
April 28, 2013 at 7:16 pm
the joys of answering early :pinch: -
The QotD question states "Which of the following hints require the word WITH? (choose 3) "
I don't use Table Hints that often, neither do I have a SQL Server 2012 installation to test, so I hit BOL and find the page on Table Hints http://msdn.microsoft.com/en-us/library/ms187373.aspx (Yes, same page the solution refers to). I read, and note the section under 'Arguments' that says 'The following table hints are allowed with and without the WITH keyword: ' - that indicates to me that the keyword is not required.
With 5 options in the answer list, my logic says if 3 require the WITH keyword, then 2 will not. (Old school maths, didn't even use a calculator for that one) So I scan the list ... NOLOCK, there's one, ROWLOCK, that's the second one, others must be out. Tick, tick, tick, submit ... "Sorry - you were wrong." What the ...?!
So I look at the BOL list again, and yes, there's XLOCK as well. So no matter which 3 I chose, I'd be wrong. There are only 2 that require the WITH keyword. The solution even says so too.
Any chance this can be fixed before a whole lot more people get miffed about losing points for a flawed (but otherwise interesting) question?
🙂 I still learnt a lot in the process.
April 28, 2013 at 9:34 pm
zerko (4/28/2013)
the joys of answering early :pinch: -The QotD question states "Which of the following hints require the word WITH? (choose 3) "
I don't use Table Hints that often, neither do I have a SQL Server 2012 installation to test, so I hit BOL and find the page on Table Hints http://msdn.microsoft.com/en-us/library/ms187373.aspx (Yes, same page the solution refers to). I read, and note the section under 'Arguments' that says 'The following table hints are allowed with and without the WITH keyword: ' - that indicates to me that the keyword is not required.
With 5 options in the answer list, my logic says if 3 require the WITH keyword, then 2 will not. (Old school maths, didn't even use a calculator for that one) So I scan the list ... NOLOCK, there's one, ROWLOCK, that's the second one, others must be out. Tick, tick, tick, submit ... "Sorry - you were wrong." What the ...?!
So I look at the BOL list again, and yes, there's XLOCK as well. So no matter which 3 I chose, I'd be wrong. There are only 2 that require the WITH keyword. The solution even says so too.
Any chance this can be fixed before a whole lot more people get miffed about losing points for a flawed (but otherwise interesting) question?
🙂 I still learnt a lot in the process.
Personally I don't recall ever writing a granularity/isolation hint with WITH (and I have written some of those hints now and again and one, TABLOCKX, many times). However I do know that the "correct" syntax for them has WITH in it, while the correct syntax for join hints doesn't have it. So although I haven't yet got into the habit of including WITH in these hints (which I must do sometime soon) I thought the question was pretty straightforward, although the wording isn't perfect.
Evidently you didn't look at the join hints page and the from page before reaching a conclusion. The other two hints listed as answer options don't come with WITH, it certainly isn't required for them as suggested by your "only 2" in bold. The three you've mentioned do have a deprecated concession that omitting WITH is allowed, so maybe the question would have better been worded using "are expected to have" rather than "require", but it's clear which 3 of these 5 hints should have WITH and which two shouldn't. Looking at the table hints page without looking at the other two relevant pages led you into the classical "a little knowledge is a dangerous thing" trap.
Tom
April 28, 2013 at 11:14 pm
Nice question 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 28, 2013 at 11:48 pm
Tom, yes I noted the deprecation issue - it's not that ...
Question says "Which of the following hints require the word WITH? (choose 3) "
Explanation says "table hints ROWLOCK, XLOCK, and NOLOCK can still be used without the "WITH" keyword", ... then ... "Index hints FORCESEEK and FORCESCAN require the "WITH" keyword."
If a hint can still be used without the WITH keyword, then it is not, by definition, required.
If the question said "(choose 2)" I would tick FORCESEEK and FORCESCAN, as per above. If the question said "which of the following hints are to be deprecated and will require ... " then the answer would be as given. It didn't. That's my issue here.
While I agree a little knowledge can be dangerous, a little confusion can be far worse.
April 29, 2013 at 12:55 am
The correct answer is wrong. Zerko is right, the explanation even says so.
Let the flaming begin 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2013 at 1:04 am
The question is: "Which of the following hints require the word WITH? (choose 3) Assume SQL Server 2012.". - Not good.
The correct answer is: FORCESCAN and FORCESEEK.
Microsoft: The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. - http://technet.microsoft.com/en-us/library/ms187373.aspx
Igor Micev,My blog: www.igormicev.com
April 29, 2013 at 1:10 am
Want my points back. The answer is those hints can be used either with "with" hint or without the with hint. My points back.
April 29, 2013 at 1:23 am
baabhu (4/29/2013)
Want my points back. The answer is those hints can be used either with "with" hint or without the with hint. My points back.
How can you have something back when you never had it in the first place? 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2013 at 2:24 am
Well, the question is obviously wrong - inverted. Based on the explanation and BOL, question is probably missing a words "do not" and instead of "Which of the following hints require the word WITH? (choose 3) " should be "Which of the following hints DO NOT require the word WITH? (choose 3)".
However, even if BOL goes to list a hints that are allowed without the WITH keyword, actually all of them are still working fine without the WITH keyword. Including FORCESEEK and FORCESCAN. You can easily check that for yourself.
http://sqlfiddle.com/#!6/20e81/5
PS: what's puzzling me is how did 45% of people get the correct answer? 🙂
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
April 29, 2013 at 2:35 am
Too bad the question author somewhere switched the two options in his head and marked the wrong answers as right and the right answers as wrong. Fortunately, I did the very same when replying, so I got a point I didn't really deserve. (Anyone who feels cheated out of a point - you can have mine! It's not like I can buy anything from those points anyway).
However, here's a far more interesting observation. I wanted to see what error message I get when omitting the word WITH for a hint that does require it - and then I found out that the documentation is incorrect. My tests indicate that, in spite of what Books Online says, FORCESCAN and FORCESEEK can be used without WITH!
CREATE TABLE abc(abx int)
GO
SELECT *
FROM dbo.abc (FORCESCAN) AS a
INNER JOIN dbo.abc AS b
ON b.abx = a.abx;
GO
SELECT *
FROM dbo.abc (FORCESEEK) AS a
INNER JOIN dbo.abc AS b
ON b.abx = a.abx;
GO
DROP TABLE abc;
go
The first query executes just fine. The second query does produce an error, but not because of the missing WITH - the error says that it's impossible to create a plan that sattisfies the hints. (Which makes sense - with no index on the table, using a seek is absolutely impossible).
(In case you're wondering why I didn't simply use a SELECT * FROM table (hint) - I tried that first, then tried if I would force a parsing error by increasing the complexity of the query).
Has anyone been able to actually get SQL Server to produce an error by using these hints without WITH?
April 29, 2013 at 2:42 am
The answer is correct. It's the question that's wrong. I'm sure the question should be "Which DO NOT require WITH" in which case everything falls into place.
April 29, 2013 at 2:43 am
baabhu (4/29/2013)
Want my points back. The answer is those hints can be used either with "with" hint or without the with hint. My points back.
You get a point for comlaining that you didn't get a point. Cheer up 🙂
April 29, 2013 at 3:28 am
I got it right by reading the documentation and assuming the question had been inverted by ascertaining that there were 3 official hints that did not require WITH, so if three answers were required it must surely be them. I agree the question is wrong though.
If what Hugo says is correct though (and I would not doubt him!) then in fact none of the answers require WITH making it a little null and void anyhow. I'm happy to review hints anyway in case I need to remember what I might need.
April 29, 2013 at 3:36 am
Richard Warr (4/29/2013)
The answer is correct. It's the question that's wrong. I'm sure the question should be "Which DO NOT require WITH" in which case everything falls into place.
I agree that the "DO NOT" part is missing. However, Hugo showed that even the answer is not correct then.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply