Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


QUERY and TABLE HINTS


QUERY and TABLE HINTS

Author
Message
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 487
Comments posted to this topic are about the item QUERY and TABLE HINTS
zerko
zerko
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 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.
TomThomson
TomThomson
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11347 Visits: 12086
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
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2569 Visits: 2764
Nice question Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
zerko
zerko
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18623 Visits: 13249
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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4488 Visits: 4943
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,
SQL Server developer at Seavus
www.seavus.com
baabhu
baabhu
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1217
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18623 Visits: 13249
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.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 778
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