SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


QUERY and TABLE HINTS


QUERY and TABLE HINTS

Author
Message
sneumersky
sneumersky
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4376 Visits: 487
Comments posted to this topic are about the item QUERY and TABLE HINTS
zerko
zerko
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 712
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.
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39785 Visits: 12894
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

kapil_kk
kapil_kk
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8399 Visits: 2777
Nice question Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
zerko
zerko
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 712
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111929 Visits: 13338
The correct answer is wrong. Zerko is right, the explanation even says so.

Let the flaming begin Smile


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Igor Micev
Igor Micev
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15862 Visits: 5178
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
baabhu
baabhu
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3497 Visits: 1218
Want my points back. The answer is those hints can be used either with "with" hint or without the with hint. My points back.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111929 Visits: 13338
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? ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
nenad-zivkovic
nenad-zivkovic
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1272 Visits: 802
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? Smile

_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search